cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors