topic Re: MTD, QTD and YTD slicers on cumulative values in DAX Commands and Tips

MTD, QTD and YTD slicers on cumulative values

dataskience — Wed, 10 Apr 2024 11:13:26 GMT

i have a dataset as follows.

Month	Particulars	Amount
Jan-24	Sales	100
Feb-24	Sales	220
Mar-24	Sales	350
Jan-24	cogs	80
Feb-24	cogs	170
Mar-24	cogs	240
Jan-24	Salary	70
Feb-24	Salary	135
Mar-24	Salary	255

All the values are cumulative.

When i select MTD for the month of mar'24, i want values of mar'24 - feb '24. for examp-le, sales for march'24 is 350-220.

when i select QTD, i want values of Mar'24 alone since it is cumulative. if the period is till Jun'24, then QTD selection should be Jun'24-Mar'24.

Can anyone help me with DAX and if possible upload a sample power bi file.

Can anyone help me with dax formula, please.

Re: MTD, QTD and YTD slicers on cumulative values

Greg_Deckler — Wed, 10 Apr 2024 12:46:54 GMT

dataskience It may be easier for you to add a calculated column that gives you the monthly values first and then standard patterns will work. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
( __Current - __Previous ) * 1.

Re: MTD, QTD and YTD slicers on cumulative values

Rupak_bi — Wed, 10 Apr 2024 12:54:24 GMT

Hope this fulfills your ask. You may use same concept in quarter calculation as MTD going forward.

Re: MTD, QTD and YTD slicers on cumulative values

dataskience — Wed, 10 Apr 2024 13:29:24 GMT

Thanks for your reply.

since there are many line items and multiple columns, the values are not calculated correctly.

Can you please provide a formula that will consider the value based on other column values also.

please