Compare values from DAX measures by a slicer selection

soldous — Wed, 29 Jan 2020 15:15:16 GMT

Hi DAX masters,

I need to achieve a little bit complicated task for me. Here is a scenario:

Slicer with Date dimension (year_month field)
When only one date is selected, calculate the difference of a measure for the selected month with the previous month
When more dates are selected, calculate the difference of a measure for the newest of selected months with the oldest of selected months
Now my solution which doesn't work:
- Measure to be compared:
  - M_volume:=Sum(Fact_Volume[Volume])
- Measure for calculation:
  - M7_DeployedVolumeChanges:=CALCULATE([M2_VolumeCurrentMonth]-[M3_VolumePreviousMonth])
- Two measures to calculate the needed values:
  - M2_VolumeCurrentMonth:=
    VAR CurrentYearMonth = MAX(Dim_Charge_date[Charge_date_SK])
    VAR CurrentYear = VALUE(MID(FORMAT(CurrentYearMonth,"General Number"),1,4))
    VAR CurrentMonth = VALUE(MID(FORMAT(CurrentYearMonth,"General Number"),5,2))
    RETURN
    CALCULATE(Fact_Volume[M_volume],FILTER(ALL(Dim_Charge_date),[YearNumber] = CurrentYear && [MonthNumber] = CurrentMonth))
  - M3_VolumePreviousMonth:=
    VAR CurrentYearMonth = MAX(Dim_Charge_date[Charge_date_SK])
    VAR CurrentYear = VALUE(MID(FORMAT(CurrentYearMonth,"General Number"),1,4))
    VAR CurrentMonth = VALUE(MID(FORMAT(CurrentYearMonth,"General Number"),5,2))
    VAR PrevioseYearMonth = MIN(Dim_Charge_date[Charge_date_SK])
    VAR PrevioseMonth = VALUE(MID(FORMAT(PrevioseYearMonth,"General Number"),5,2))
    VAR PreviouseYear = VALUE(MID(FORMAT(PrevioseYearMonth,"General Number"),1,4))
    RETURN
    IF(CurrentYear = PreviouseYear && CurrentMonth = PrevioseMonth,
    IF(CurrentMonth=1,
    CALCULATE(Fact_Volume[M_volume],FILTER(ALL( Dim_Charge_date),[YearNumber]= CurrentYear - 1 && [MonthNumber] = CurrentMonth + 11)),
    CALCULATE(Fact_Volume[M_volume],FILTER(ALL( Dim_Charge_date),[YearNumber]= CurrentYear && [MonthNumber] = CurrentMonth - 1)))
    ,
    CALCULATE(Fact_Volume[M_volume],FILTER(ALL(Dim_Charge_date),[YearNumber] =PreviouseYear && [MonthNumber] = PrevioseMonth)))
- Dim_Charge_date is a Date dimension and one attribute from this table is in the slicer.
- Charge_date_SK is the only one numerical attribute which could be used for comparison and has this format: YYYYMMDD

I hope that the scenario is understandable. Now I have an issue:

There is something wrong with M3_VolumePreviousMonth. I receive an error: Cannot convert value '' of type Text to type Number and don't know why.
Could you please check if the concept is correct and if not tell me what's wrong with it?

Thank you very much in advance.

Zdenek

Re: Compare values from DAX measures by a slicer selection

soldous — Wed, 29 Jan 2020 15:36:07 GMT

Ok, so the error was on my side in the Date dimension.

There was Charge_date_SK with value -1 which caused the error.

I leave the solution here for inspiration.

topic Compare values from DAX measures by a slicer selection in DAX Commands and Tips

Compare values from DAX measures by a slicer selection

Re: Compare values from DAX measures by a slicer selection