DAX to get YTD for all the months until selected month with Month-year slicer

[Poulg]

Hi, 

I have created a measure to get the YTD for the selected MMM-YY slicer below

YTD Sales = CALCULATE(SUM(Actual[Premium]),DATESYTD('Calendar'[Date])

I'm using MMM-YY slicer in my report. Also, Monthname from Calendar table in X-Axis, Y-Axis is YTD premium measure I have created.

Now, here when I select July-22, I want to get the Bar chart from Jan to July as I'm getting only July when I select Jul-22 in my MMM-YY slicer.
Please advise.


Thanks 

[Poulg]

@amitchandak

@Ashish_Mathur

[Poulg]

Hi @Ashish_Mathur 

Thanks for the advice, But that is giving the value for the selected month only.
I want from January till the selected month.

similar post -Based on Month Slicer want to show YTD trend as Mo... - Microsoft Power BI Community

[Ashish_Mathur]

Share the download link of the PBI file.

[Ashish_Mathur]

Hi,

You do not need the YYYY-MM slicer.  To the X-axis, drag year and Month name from the Calendar Table.  Your measure is fine. 

[Greg_Deckler]

@Poulg Better Year to Date Total - Microsoft Power BI Community

[Poulg]

Hi @Greg_Deckler 

Thanks for the swift response to my post. 
But this video is just showing how to create YTD which doesn't meet my criteria.

Thanks.

[Greg_Deckler]

@Poulg If I am understanding what you want, you would have to implement that as a disconnected date table for the slicer. That way you could get the MAX of that and zero out any thing that is beyond that date. Basically an extra IF statement at the end of the measure.

[Poulg]

@Greg_Deckler 
Same as this criteria. But, I'm using a Bar chart and MMM-YY slicer

I tried this but not working
Show Graph From First Month until filtered Month of the Year in Slicer -Power BI Tutorial (40/50) - ...

[denxx34]

Hi @Poulg 

thx for the hint to the video... i have the same problem.

My solution:

1. Year&Month in the slicer from Filter-Date-Table to filter 

2. Year&Month in the visual from the normal Date-Table + the new measure

3. Filter-Date-Table is disconnected with other model

Measure:

YTD Turnover Filter =

VAR __ymax = YEAR( MAX( FilterDate[date] ) )

VAR __mmax = MONTH( MAX( FilterDate[date] ) )

VAR __dmax = MAX( FilterDate[date] )

RETURN

CALCULATE( [YTD Turnover] ,

    FILTER( Date , YEAR( Date[date] ) = __ymax

    && MAX( Date[monthnumber]) <= __mmax

    && MAX( Date[date] ) <= __dmax

    )

)