cancel
Showing results 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

## 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

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.

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.