Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jburklund
Helper III
Helper III

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

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).

 

NamePaycheck DateYTD Paycheck Amount
Julie2/1/20201000
Julie3/1/20201500
Julie4/1/20201750
Tim2/1/2020500
Tim3/1/2020750
2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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.

View solution in original post

harshnathani
Community Champion
Community Champion

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

 

1.jpg

 

 

Regards,
Harsh Nathani

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

View solution in original post

4 REPLIES 4
harshnathani
Community Champion
Community Champion

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

 

1.jpg

 

 

Regards,
Harsh Nathani

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

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

amitchandak
Super User
Super User

@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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.