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

Visual interactions selected date overruling actual and forecast

I'm having a challenge to find a way to present the Orders to-date on different visuals (bar and Gauge measure).

 

The solution I'm looking for is to reflect in the Guage visual based on the following:

  1. When the report opens (or no filter selected from other visual), Guage presents the Orders based on the Actual table ie. summing all "Actual" orders
  2. When I select a certain month on the Stacked Column visual, the Guage gives the accumulated orders till that selected month including Actual + forecast

Ramiroz_0-1600102308958.png

 

Ramiroz_1-1600102414107.png

What I want, is when I select October the Guage measure will show accumlated sum all the way to October (though it's still forecasted) ie. = 1000. Or selecting December will show the maximum ie. 1200.

Ramiroz_2-1600102455736.png

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMDJR0lQwMQ6ZhcUpqYoxSrA5E1witrjFfWBK+sKV5ZM7yy5nhlLfDKWqLKuuUXpSYnFpfA5A0NCMgbEpA3wiUfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Orders = _t, #"Actual/Forecast" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Orders", Int64.Type}, {"Actual/Forecast", type text}})
in
    #"Changed Type"

 

 

 

 

I've defined 2 DAX measures for the Guage as follows:

 

 

 

Actual to-date = CALCULATE(SUM(Monthly_Orders[Orders]), Monthly_Orders[Actual/Forecast] = "Actual", DATESBETWEEN(Monthly_Orders[Date],DATE(2020,1,1), MAX(Monthly_Orders[Date])))

Maximum = CALCULATE(SUM(Monthly_Orders[Orders]), ALL(Monthly_Orders))

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @Ramiroz ,

 

Try the following code:

Actual-To-date =
IF (
    ISFILTERED ( Query1[Date] );
    CALCULATE (
        SUM ( Query1[Orders] );
        FILTER (
            ALL ( Query1[Date]; Query1[Actual/Forecast] );
            Query1[Date] <= MAX ( Query1[Date] )
        )
    );
    CALCULATE ( SUM ( Query1[Orders] ); Query1[Actual/Forecast] = "ACTUAL" )
)

 

MFelix_0-1600158077349.pngMFelix_1-1600158102722.png

Check PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

Hi @Ramiroz ,

 

Try the following code:

Actual-To-date =
IF (
    ISFILTERED ( Query1[Date] );
    CALCULATE (
        SUM ( Query1[Orders] );
        FILTER (
            ALL ( Query1[Date]; Query1[Actual/Forecast] );
            Query1[Date] <= MAX ( Query1[Date] )
        )
    );
    CALCULATE ( SUM ( Query1[Orders] ); Query1[Actual/Forecast] = "ACTUAL" )
)

 

MFelix_0-1600158077349.pngMFelix_1-1600158102722.png

Check PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.