cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mighty_mouse Frequent Visitor
Frequent Visitor

HELP!! Switch for Period Selection to change multiple columns

Capture1.jpg

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

4 REPLIES 4
v-jiascu-msft Super Contributor
Super Contributor

Re: HELP!! Switch for Period Selection to change multiple columns

Hi @mighty_mouse,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft Super Contributor
Super Contributor

Re: HELP!! Switch for Period Selection to change multiple columns

Hi @mighty_mouse,

 

Could you please mark the proper answers as solutions?

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mighty_mouse Frequent Visitor
Frequent Visitor

Re: HELP!! Switch for Period Selection to change multiple columns

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

v-jiascu-msft Super Contributor
Super Contributor

Re: HELP!! Switch for Period Selection to change multiple columns

Hi @mighty_mouse,

 

Can you share a sample, please?

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 60 members 1,294 guests
Please welcome our newest community members: