Compare values from DAX measures by a slicer selection

[soldous]

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

[soldous · Accepted solution]

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.