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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.