Calculate YTD , MTD, WTD, Transaction Count in Power BI

I have 1 table with multiple rows. It looks something like this:

------------------------------------------------ StoreId| PostingDate | SalesAmt MAIN | 2021-02-04 | 100 WEST | 2021-08-11 | 15 WEST | 2021-09-11 | 36 MAIN | 2021-11-11 | 78 MAIN | 2021-04-11 | 56 ------------------------------------------------ 

And soon and so forth...

Now I want to produce the following in the Power BI as Table:

-------------------------------------------- StoreId| YTD | MTD | WTD | TransactionCount | WEST |5,447| 800 | 74 | 1,475 | MAIN |4,500| 421 | 15 | 1,855 | -------------------------------------------- 

How can I achieve that? I am very new to this so I don't know how to do it. I have been reading DAX and Power Query but maybe DAX is suitable for this?

1 Answer

I assumed your data looks like this. I've added data for the year 2022. Also, I'm assuming there aren't future dates, the observations behave as transactions that happened in the past.

Table

Calendar Table

For the calculations below to work, you need to create a calendar table. It goes from the first date of Table until today. If your calendar table is different the time intelligence function will not work.

Calendar = CALENDAR(MIN('Table'[PostingDate]),TODAY()) 

And mark the Calendar table as a Date Table.

Sales Amount

Sales Amount = sum('Table'[SalesAmt]) 

WTD

Assumes your week starts on Monday.

WTD = VAR WeekStart = TODAY() - WEEKDAY(today(),2) RETURN CALCULATE([Sales Amount],'Table'[PostingDate]>=WeekStart) 

MTD:

MTD = TOTALMTD([Sales Amount],'Calendar'[Date]) 

YTD

YTD = TOTALYTD([Sales Amount],'Calendar'[Date])