Using YTD and Date columns to find a single month's amount

[jburklund]

I am trying to write a measure to find a single paycheck's amount given I only know the YTD amount for several paychecks. For example, in the sample below, Julie's paycheck in March would be $500 (1500-1000) and Tim's paycheck in March would be $250 (750-500).

Name	Paycheck Date	YTD Paycheck Amount
Julie	2/1/2020	1000
Julie	3/1/2020	1500
Julie	4/1/2020	1750
Tim	2/1/2020	500
Tim	3/1/2020	750

[amitchandak · Accepted solution]

@jburklund , with date calendar

MTD = CALCULATE(SUM(table[Paycheck Amount]),DATESMTD('Date'[Date]))
last MTD = CALCULATE(SUM(Table[Paycheck Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

diff = [MTD ]-[last MTD ]

Power BI — MTD Questions — Time Intelligence 3–5
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[harshnathani · Accepted solution]

Hi @jburklund ,

Incase you want a Calculated Column

Column = 
var a = CALCULATE(MAX('Table'[YTD Paycheck Amount]), FILTER('Table','Table'[Name] = EARLIER('Table'[Name]) && 'Table'[Paycheck Date] < EARLIER('Table'[Paycheck Date])))
RETURN
'Table'[YTD Paycheck Amount] - a

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[jburklund]

Thanks so much @harshnathani this was a great solution as well! 

[amitchandak · Accepted solution]

@jburklund , with date calendar

MTD = CALCULATE(SUM(table[Paycheck Amount]),DATESMTD('Date'[Date]))
last MTD = CALCULATE(SUM(Table[Paycheck Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

diff = [MTD ]-[last MTD ]

Power BI — MTD Questions — Time Intelligence 3–5
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[jburklund]

@amitchandak  Thank you so much! I will definitely use your provided resources to learn more. This solved my problem. Thanks again for the quick reply and easy to follow solution - much appreciated.