Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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)
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 , 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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |