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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors