Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
soldous
Advocate II
Advocate II

Compare values from DAX measures by a slicer selection

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

1 ACCEPTED SOLUTION
soldous
Advocate II
Advocate II

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.

 

 

View solution in original post

1 REPLY 1
soldous
Advocate II
Advocate II

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.

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors