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

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

Announcements

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

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### 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
Top Kudoed Authors