Context in DAX measure

[karo]

Hi,

I would like to obtain symilar effect for table like in case of dynamic measure selector for charts. So far I was able to obtain the view inserted below, however I need to find a way to add Actual data for past months to Forecast as well (without making a copy of Actuals). And I am strugling with filter/row context.  Anyone can help, please?

Regards,

Karo

[v-easonf-msft]

Hi, @karo 

You’re welcome.

If  you have solved problem, please mark the solution to close this thread.

If you haven't, please feel free to ask.

Best Regards,
Community Support Team _ Eason

[v-easonf-msft]

Hi, @karo 

Change your measure [All] as below:

All2 = 
VAR A =
    IF (
        ISBLANK ( [Forecast] ),
        VAR tab =
            SUMMARIZE (FILTER( ALL ( Actuals ), [Date]>=DATE(2020,09,01)),Actuals[Date], [Value] )
        RETURN
            SUMX (
                FILTER (
                    tab,
                    YEAR ( [Date] ) = SELECTEDVALUE ( 'Calendar'[Date].[Year] )
                        && MONTH ( [Date] ) = SELECTEDVALUE ( 'Calendar'[Date].[MonthNo] )
                ),
                [Value]
            ),
        [Forecast]
    )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Type] ),
        SWITCH (
            SELECTEDVALUE ( 'Table'[Type] ),
            "Actuals", [Actuals],
            "Budget", [Budget],
            "Forecast", A
        ),
        [Actuals] + [Budget] + A
    )

If it doesn't meet your requirement,please feel free to let me know.

Best Regards,
Community Support Team _ Eason

[karo]

@v-easonf-msft  thank you so much! I will try to replicate your DAX in my report based on "real" data and in case of doubts I will go back

[v-easonf-msft]

Hi, @karo 

Measure [all] is composed of three other measures.

Is it convenient to show its code?

Best Regards,
Community Support Team _ Eason

[karo]

Hi @v-easonf-msft , Yes and sure. I have prepared dummy .pbix based on Power BI Sample Data. In my real scenario Budget,Actuals and Forecast data are comming from different systems. What is the best way to share the pbix file ?

Regards,

Karo

[v-easonf-msft]

Hi, @karo 

For data security considerations, it is recommended that you upload the file to Onedrive for Bussiness and share the link.

Best Regards,
Community Support Team _ Eason

[karo]

@v-easonf-msft  I have shared the link with you via private message. Thanks a lot for looking into  my case!

[PaulDBrown]

@karo 

You need to create a measure to include actuals if the filter context if before the current month, or the forecast value if it is after.

Something along the lines of:
Combined = 
VAR YM = YEAR(MAX('Calendar '[Date])) * 100 + MONTH(MAX('Calendar '[Date]))

VAR CurrentYM = YEAR(TODAY()) *100 + MONTH(TODAY())
RETURN
SUMX(Calendar, IF(YM < CurrentYM, [your actuals measure], [your forecast measure])

[karo]

Hi @PaulDBrown , thank you for reply, however it is not solving my issue.  Still context (please refer to 'Table'[Type] from my screens) is an issue.

Regards,

Karo

[PaulDBrown]

@karo 

Is there a reason you need the slicer for type on this visual?

You can create a matrix with the values on rows like this:

Using the option under "Values" the Format pane and:

The measures I used to create the Measure to show sales or forecast:

1) The if measure to select sales or forecast:

calc sales or forecast = 
VAR YM = YEAR(MAX('Calendar Table'[Date])) * 100 + MONTH(MAX('Calendar Table'[Date]))
VAR CurrentYM = YEAR(TODAY()) *100 + MONTH(TODAY())

RETURN
IF(YM < CurrentYM, [Sum Sales], [Sum Forecast])

2) The final measure:

Combined Sales and Forecast =
SUMX (
    SUMMARIZE (
        'Calendar Table',
        'Calendar Table'[YearMonth],
        "Combined", [calc sales or forecast]
    ),
    [Combined]
)

[karo]

Hi @PaulDBrown  Yes, there is 🙂 I am trying to obtain similar effect for table like in case of dynamic measure selector for charts (please refer to https://visualbi.com/blogs/microsoft/powerbi/dynamic-measure-selection-power-bi/ as an example) . 

Regards,

Karo

[PaulDBrown · Accepted solution]

@karo 

Ok. Here is one way.

with a Type table which is disconnected from other tables in the model:

Create new measure as follows (I'm referencing the measures posted in my previous reply):

Filtered measures =
SWITCH (
    TRUE (),
    MAX ( 'Type Selection'[Type] ) = "Sales", [Sum Sales],
    MAX ( 'Type Selection'[Type] ) = "Target", [Sum Target],
    [Combined Sales and Forecast]
)

Add the "Type" column from the Type selection table to the row bucket of a matrix, the year and month columns from the calendar table as columns, and the [Filtered measures] measure to the "Values" bucket.

and you will get the following:

[karo]

@PaulDBrown  Big, big thank you! I replicated your steps and it seems that it can be a solution.