Max month for max year measure

[cosminc]

Hi all,

I have data for 2 years 2017 (jan-oct) and 2016 (jan-dec) - columns year, month and a sum and i want to vizualize a graph and a month filter with data ytd also for 2016.

I think i need to make a measure with max month of max year but i don't know how. After that,how can be input in filter?

I'm new with Power BI, maybe it's very easy but i don't know how to manage; it's not like in qlikview i suppose

can anyone help me please with this issue?

Thanks in advance,

Cosmin

[v-ljerr-msft · Accepted solution]

Hi @cosminc,

If I understand you correctly, you can firstly add a Year-Month column in your table.

Year-Month = 'Table1'[year] * 100 + 'Table1'[month]

And use the formula below to create a measure to calculate the YTD sum.

YTD =
CALCULATE (
    SUM ( 'Table1'[sum] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[year] = MAX ( 'Table1'[year] )
            && 'Table1'[month] <= MAX ( 'Table1'[month] )
    )
)

Note: You'll need to replace 'Table1' with your real table name.

Then you should be able to show Year-Month column as Axis, the measure [YTD] as Values on a chart visual. And use Year-Month column or just year column as Slicer on your report. 

Regards

[surfingjoe]

Most articles I've run into for YTD for some reason I have to apply a visual filter for the current year.  This is the first article where I do not have to apply a filter to a visual based on YTD, because its based on max year in the calendar table or in this case the "Dates" table.

The only additional change that I would add to this, is to use the Month Number for Max Month.  The Max function needs a numeric value, doesn't work with text like month name.

Use the following in the forumula:

&& Dates[Month Number]<= MAX ( Dates[Month Number]) 

 and it will be an integer comparison, then it works great.