cancel
Showing results for
Did you mean:
Highlighted
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).

 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
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV

## 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]))

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://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...

Proud to be a Super User!

Highlighted
Super User III

## 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``````

Regards,
Harsh Nathani

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

4 REPLIES 4
Highlighted
Super User IV

## 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]))

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://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...

Proud to be a Super User!

Highlighted
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 III

## 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``````

Regards,
Harsh Nathani

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

Highlighted
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!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors