Need Dax(measure) help. Calculate sum for dynamic YTD and PYTD

[dbowman]

Using a filter, users are selecting a Year-Month value in format YYYY-MM.

This filter is coming for a custom column on the CALENDAR table created in PBI.

We have to columns on our report table visual for Clients YTD, and Clients PYTD.

YTD = Jan 1. through the Year-Month selected in the filer.

PYTD = YTD-12 months

My data is correct, but i can not comeup with a proper DAX formula to get the end SUM results correct.

Any help is appreciated!

Curently i am trying with

Clients YTD = CALCULATE(TOTALYTD(SUM(Metrics[Client Count])+0,'Calendar'[Date]))

Clients Prev YTD = TOTALYTD(SUM('Metrics'[Client Count])+0,DATEADD('Calendar'[Year-Month],-12,MONTH))

[bolfri · Accepted solution]

Here is your measures:

Client Count = SUM(Metrics[Client Count])

YTD = TOTALYTD(SUM(Metrics[Client Count]),'Calendar'[Date])

PYTD = CALCULATE([YTD],SAMEPERIODLASTYEAR('Calendar'[Date]))

Results:

[SivaMani]

@dbowman Try this,

• YTD

YTD = TOTALYTD(SUM(Metrics[Client Count]),LASTDATE(Calendar[Date])

• PY

PY = CALCULATE(SUM(Metrics[Client Count]),PREVIOUSYEAR(LASTDATE(Calendar[Date])), ALL(Calendar))

[dbowman]

@SivaMani - Thank you for for quick response. Unfortunately, this did not solve it. Although better, the numbers are still off.

My results becoming back are:

YTD - 2

PY - 3

Data Table

ID	Year-Month	Client Count
12345	2021-1	2
12345	2021-2	1
12345	2021-3	2
12345	2021-9	1
12345	2022-2	5
12345	2022-3	2
12345	2022-7	1

If i select in filter 2022-9

Expected results should be:

YTD - 8

PY - 6

Or selecting 2022-7

YTD - 8

PY - 5

Thanks for your help!

[v-yueyunzh-msft]

Hi ,  @dbowman 

According to your description , you want to "Calculate sum for dynamic YTD and PYTD".

Here are the steps you can refer to :
(1)This is my test data:

(2)We need to click "New Table" to  create a date table as  a slicer:

Date = ADDCOLUMNS( CALENDAR(FIRSTDATE('Table'[Date]) ,DATE(2022,12,31)) , "Year_Month" ,FORMAT([Date],"YYYY-MM"),"Year_Month_num",YEAR([Date])*100+MONTH([Date]))

And we need to create a relationship between two tables by [Date].

(3)Then we need to create two measures:

PY = var _cur_date = MAX('Date'[Date]) return CALCULATE( SUM('Table'[Value]) , YEAR('Date'[Date])=YEAR(_cur_date)-1 && MONTH('Date'[Date])<= MONTH(_cur_date))

YTD = var _cur_year_month = MAX('Date'[Date]) return CALCULATE( SUM('Table'[Value]) , YEAR('Date'[Date])=YEAR(_cur_year_month) && MONTH('Date'[Date])<= MONTH(_cur_year_month))

(4)Then we can put the fields we need on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly