Calculating a Measure and filter by a var which should stay default until a filter is introduced

[Anonymous]

Hi Experts. 

I have a tabular model cube built in SSAS

I have a scenario where I have a fact table with lots of data linked to a date dimension. I am calculating some measure [X] on the date dimension to tell me how many days was available within a certain period.

This is a fixed period shown onto the date dimension that spands for 3 years. (Date dim on daily granularity)

Now My client wants to be able to select a YYYYMM type slicer on PowerBI front-end where this cube is exposed.

I need to calculate the amount of days from the start of the 3 year period where the YYYYMM filter is sliced would fall in up until the last day of that month.

For example:

My 3 y period starts 1 jan 2016 up until 31 dec 2018

User selects 201803

measure must SUM [Is_Workday] column on dimperiod between start of period till selected date on slicer.

DateDim looks as follows

|---------------------------------------------------------------------------------|

PeriodKey        Datekey            PeriodCycle    YearMonth       IsWorkDay     

        1           2016/01/01                42                201601                0

        2           2016/01/02                42                201601                1

        2           2016/01/03                42                201601                1

        ............continues

        1000    2019/01/01                 43                201901                1

|---------------------------------------------------------------------------------|

Measure must return sum(Isworkday)   where **[SlicerSelectedDate] is >  Datekey   and also where PeriodCycle of 

**[SlicerSelectedDate] = PeriodCycle of Datekey in dimension between this period

Please ask away if i am unclear.

Regards

Arnoux

[Anonymous · Accepted solution]

HI @Anonymous,

You can try to use following measure if it suitable for your scenario:

Measure =
VAR selected =
    SELECTEDVALUE ( Date[date] )
VAR selectedPeriod =
    CALCULATE (
        MIN ( Table[PeriodCycle] ),
        FILTER ( ALLSELECTED ( Table ), [DateKey] = selected )
    )
RETURN
    CALCULATE (
        SUM ( Table[IsWorkDay] ),
        FILTER (
            ALLSELECTED ( Table ),
            [DateKey] <= selected
                && [PeriodCycle] = selectedPeriod
        )
    )

If not help, please provide some sample data for test and coding formula.

Regards,

Xiaoxin Sheng