cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

Accepted Solutions
Highlighted
Super User IX
Super User IX

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

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Super User V
Super User V

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

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
Highlighted
Super User IX
Super User IX

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

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Helper II
Helper II

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

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

Highlighted
Super User V
Super User V

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

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

Highlighted
Helper II
Helper II

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

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors