Integrating a certain measure to matrix table to show data on the unselected month slicer

[robderay]

Hi team - 

Kindly request for your assistance on scenario I'm trying to create visual on my report. The thing was I'm using Net Profit to show on matrix visual with 2 slicers (one for Financial Year, and one for text based month). Then the thing was I want to add another measure which is to show the budget for those unselected months. For e.g. on my table I had click month from Jul-Nov FY20. And then I wanted to add or to use if conditions to show the budget based from budget summary table to those unselected months. I already create disconnected table but can't get to add the budget measure on the matrix table? 

[v-shex-msft · Accepted solution]

HI @robderay,

You can try to use except function to extract the unselected value and stored to a variable, then you can add 'all' filter on your table with conditions to check records in the variable list.

measure =
VAR unselected =
    EXCEPT ( ALL ( Table[Column] ), ALLSELECTED ( Table[Column] ) )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALL ( Table ), [Column] IN unselected )
    )

Dax EXCEPT 

The IN operator in DAX 

If above not help, can you please share some sample data for test?

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[amitchandak]

I hope you had a date calendar.

Rolling 4 = CALCULATE(sum(Budget[Budget Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),4,MONTH))

Rolling 4 = CALCULATE(sum(Budget[Budget Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date])+1,-4,MONTH))

Now if you can find a variable to get no month selected and use in place of 4

month diff = datediff(min(Date[Date]),max(Date[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[robderay]

I'm not sure if I'm getting this? Do the rolling measure would show those months that was not selected on the filter and get the budget for those months and still can be visualized on the repor? 

[amitchandak]

Typically date table can move around, if not we can use all(date) in the filter. If it does not work just share sample data and sample output.