YTD measure for month

[jockefe]

I am using a SSAS OLAP as data source and have a couple of YTD measures which I want to use in Power BI

My PBI reports are all based on a month slicer (typically the business want to look at the data from a month perspective)

YTD are defined with the YTD MDX function shich means that I am using a date heirarchy year - month

This works all fine but it requires me to include a slicer for year (or a heirarchy slicer) in the report

If possible I want to remove the year slicer (space is pressures in the reports)

Any ideas?

Workarounds in PBI?

Posibble to define a YTD measure without teh YTD function?

[SivaMani]

@jockefe,

YTD will not work without year. Year To Date is related to year so it definetly required year to calculate YTD. We can't calculate YTD using only month, Right?

Hope, I made it clear.

[jockefe]

Thanks, I understand that.

But given that I have a year - month field (e.g. 2017 january)

Are there any workarounds? either in Power BI or by changing the MDX

[SivaMani]

I think, no.

Because you have to use a year to calculate YTD. So whenever you try to filter the YTD based on Date, it needs year to calculated YTD for that particular period.

Let's say you have data for 2016 and 2017. You have calculated YTD.

You have month slicer.

For example, you selected Nov in the slicer. How do you whether it's Nov 2016 or Nov 2017.
How do you calculate the YTD without know the year?

Hope, I'm clear.

[jockefe]

you are clear, thanks

But since the year-month field contains the year I thought it might be possible to extract the year from that field and use if for the YTD

[v-ljerr-msft]

Hi @jockefe,

---

But since the year-month field contains the year I thought it might be possible to extract the year from that field and use if for the YTD

---

If I understand you correctly, you should be able to create a new measure to calculate the YTD with the year-month field. The formula below is for your reference. 

measure_YTD =
VAR lastDay =
    MAX ( 'Date'[Date] )
VAR year =
    YEAR ( lastDay )
RETURN
    CALCULATE (
        SUM ( 'Table1'[Values] ),
        FILTER (
            ALL ( 'Date' ),
            YEAR ( 'Date'[Date] ) = year
                && 'Date'[Date] <= lastDay
        )
    )

Regards

[jockefe]

thanks! That what I mean.

But I am using SSAS OLAP (Live connection) which means that I need to translaet that calculation ito MDX