YTD with month slicer

[ab_99]

Hi PBI Community,

I'm stuck with the following issue.

In my report, year and month are selected by using two slicers.
Based on this selection, a graph with YTD values up to the selected month should be displayed - including all previous months from the beginning of the year.
So I need a measure which calculates the YTD values over the month up to the end of the selected month.

How can this be done? 

Thanks for your help in advance!

Best,

An

[amitchandak · Accepted solution]

@ab_99 , You need to use the disconnected table in the slicer.

//Date1 is independent/disconnected Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -1*month(_max) )
return
calculate( totalmtd(sum(Table[Value]),'Date'[Date]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

//Date1 is independent/disconnected Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -1*month(_max) )
return
if(Max('Date'[Date]) >=_min && ('Date'[Date]) <=_max, calculate( sum(Table[Value]),datesmtd('Date'[Date])) )

[ab_99]

@mh2587 thank you for your response! Sadly, its giving this result (as the month slicer is filtering the visual): 

I need the privious months to be displayed too...

[amitchandak · Accepted solution]

@ab_99 , You need to use the disconnected table in the slicer.

//Date1 is independent/disconnected Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -1*month(_max) )
return
calculate( totalmtd(sum(Table[Value]),'Date'[Date]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

//Date1 is independent/disconnected Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -1*month(_max) )
return
if(Max('Date'[Date]) >=_min && ('Date'[Date]) <=_max, calculate( sum(Table[Value]),datesmtd('Date'[Date])) )

[ab_99]

@amitchandak Thank you for the help!

Unfortunately, your solution doesn't work - or maybe I got something wrong. Could you please add some more details to your solution? 

[Anonymous · Accepted solution]

Hi @ab_99 ,

Attached is the demo file, and hope this helps you understand @amitchandak 's approach.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Ewa_28]

hi, 
Thank you for your demo. It was usefull for me and I solved the problem!! 🙂

[mh2587]

YTD Value = //This might give an idea
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
VAR SelectedMonth = SELECTEDVALUE('Date'[MonthNumber])
RETURN
CALCULATE(
    SUM('YourTable'[Value]),
    FILTER(
        ALL('Date'),
        'Date'[Year] = SelectedYear &&
        'Date'[MonthNumber] <= SelectedMonth
    )
)