Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am building a dashboard that I would like the columns (Chargeable Kgs, Airline Revenue, Airline revenue PK) in the table to switch from YTD to MTD for each measure when I hit the MTD and YTD switch this is what my dashboard looks like right now.
I've created a separate time dimmension table for the period selection of MTD and YTD, Ive also created a separate table for the measurement selection. (Chargeable Kgs, Airline Revenue, Airline revenue PK)
Right now the tables are not effected by the period switch. This is what the period switch looks like now and its relationships:
Period Selection = IF(ISCROSSFILTERED('Time Dimmension'[Period]),SWITCH(TRUE(),
VALUES('Time Dimmension'[Period]) = "YTD", [YTD 2018],
VALUES('Time Dimmension'[Period]) = "MTD", [MTD 2018],
BLANK(),BLANK()))
YTD 2018 = TOTALYTD('Measure Dimmension'[Measure Selection],'Calendar'[Date])
YTD 2018 = TOTALYTD('Measure Dimmension'[Measure Selection],'Calendar'[Date])
Measure Selection = IF(ISCROSSFILTERED('Measure Dimmension'[Measure]),SWITCH( TRUE(),
VALUES('Measure Dimmension'[Measure]) = "Airline Revenue PK", [Total Airline Revenue PK],
VALUES('Measure Dimmension'[Measure]) = "Total Airline Revenue", [Total Airline Revenue],
VALUES('Measure Dimmension'[Measure]) = "Total Chrg KGs", [Total Chargeable Kilos],BLANK()),BLANK())
Current measurements in the tables
Airline Revenue PK YoY% =
IF(
ISFILTERED('Calendar'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_YEAR = CALCULATE([Total Airline Revenue PK], DATEADD('Calendar'[Date].[Date], -1, YEAR))
RETURN
DIVIDE([Total Airline Revenue PK] - __PREV_YEAR, __PREV_YEAR)
Airline Revenue PK YTD = TOTALYTD([Total Airline Revenue PK], 'Calendar'[Date].[Date])
Airline Revenue YoY% =
IF(
ISFILTERED('Calendar'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_YEAR =
CALCULATE(
SUM('Data'[Airline Revenue]),
DATEADD('Calendar'[Date].[Date], -1, YEAR)
)
RETURN
DIVIDE(SUM('Data'[Airline Revenue]) - __PREV_YEAR, __PREV_YEAR)
)
Airline Revenue YTD = TOTALYTD(SUM('Data'[Airline Revenue]), 'Calendar'[Date].[Date])
Chargeable Weight YoY% =
IF(
ISFILTERED('Calendar'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_YEAR =
CALCULATE(
SUM('Data'[Chargeable Weight]),
DATEADD('Calendar'[Date].[Date], -1, YEAR)
)
RETURN
DIVIDE(SUM('Data'[Chargeable Weight]) - __PREV_YEAR, __PREV_YEAR)
)
)
Chargeable Weight YTD = TOTALYTD(SUM('Data'[Chargeable Weight]), 'Calendar'[Date].[Date])
For all these measures with YTD I have already created the same measures for MTD
Hi @Anonymous,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
Hi @Anonymous,
Seems you want to make switch slicer to work. I would suggest you try a formula like below.
Measure = IF ( SELECTEDVALUE ( 'SelectionTable'[Column] ) = "MTD", TOTALMTD ( SUM ( FactSales[SalesQuantity] ), 'Calendar'[Date] ), TOTALYTD ( SUM ( FactSales[SalesQuantity] ), 'Calendar'[Date] ) )
Measure = IF ( SELECTEDVALUE ( 'SelectionTable'[Column] ) = "MTD", [Measure 1], [Measure 2] )
Best Regards,
Dale
@v-jiascu-msft When I try to use this as a solution, the data only shows me YTD results when I select MTD the results are blank.
Hi @Anonymous,
Can you share a sample, please?
Best Regards,
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |