Lost in various applied context in a DAX measure: NEED help

[BB76]

Dear All,

I have a fairly simple problem - but i am lost and don't get the solution.

I have

• 1 factable IHB with various columns
• 1 simple dimension table

My dummy report page does on include one table visual populated with IHB[Columns] and one slicer with date dimension. Slice can have multiple selections on date.

I want to be able to calculaton previous day amount of column 'IHB'[Amount (Curr)] with the below dax measure this works if i select more than one date. If i do only select one day (which should be base case usage) the measure is kept blank:

Can some please help? I have tried various approaches to tweak applied contexts, but faild:

PreviousDayAmountMeasure = 
VAR CurrentDate = MAX('IHB'[Date])
VAR PreviousDate = CALCULATE(
                        MAX('IHB'[Date]), 
                        FILTER(ALL('IHB'), 
                        IHB[Date] < CurrentDate)
                    )
VAR PreviousDayAmount = CALCULATE(
                            SUM('IHB'[Amount (Curr)]), 
                            ALL('IHB'[Date]), 
                            'IHB'[Date] = PreviousDate,
                            ALLSELECTED('Date'[Datum])
                        )
RETURN IF(ISBLANK(PreviousDate), BLANK(), PreviousDayAmount)

[BB76]

Thank you very much @Prateek97  - highly appreciated.

One final question - is there an easy way to filter the next previous date which is not blank?

[Prateek97]

Hi @BB76 ,

Thank you for marking my reply as the solution.

Regarding your query, I think you want to filter the table visual to only show dates against which we have a sales value. You can apply a visual level filter on your original Sales column to show where Sum of Sales is not blank.

[Prateek97]

Hi @BB76 ,

If I removed the ALLSELECTED('Date'[Datum]) from PreviousDayAmount variable, it seems to be working fine.

Any specific reason for adding it?

[BB76]

it still doesn't work:
Slicer 1 dateSlicer 2 dates

[Prateek97]

Hi @BB76 ,

I think what is happening is you have used a different 'datum' column in your date slicer. When you select only 1 value it will only have one date in the context and hence there will not be any previous day.
can you use the same date as in the table in the slicer as well?

[BB76]

Hi @Prateek97 ,

you are totally right and when changing slicer Date with the one from the facttable, it works. But the original approach has been done on purpse given that i have central Date DimTable which i use accors various FactTables and Repotslide. Any idea how to overcome this problem?

[Prateek97]

Hi @BB76 ,

Please use the below DAX.

PreviousDayAmountMeasure Using DimDate = 

VAR CurrentDate = MAX('Calendar'[Date])

VAR PreviousDate = CALCULATE(

                        MAX('Calendar'[Date]), 

                        FILTER(ALL('Calendar'), 

                        'Calendar'[Date] < CurrentDate), FILTER(ALL('Sales Table'),'Sales Table'[SalesPrice]<>BLANK())

                    )

VAR PreviousDayAmount = CALCULATE(

                            SUM('Sales Table'[SalesPrice]), 

                            ALL('Calendar'[Date]), 

                            'Calendar'[Date] = PreviousDate

                        )

RETURN

IF(ISBLANK(PreviousDate), BLANK(), PreviousDayAmount)

Here, I have added the DimDate column - Calendar[Date] in my case. Also, please ensure that in the Table Visual you are using the DimDate instead of the Fact Date.

Please let me know if it works.

[BB76]

Hi @Prateek97 ,

it does not really do the trick:

the measure should show up the previous day amount  not on that day, but on the current day.

[Prateek97 · Accepted solution]

Hi @BB76 ,

I think you are missing the DimDate in your visual as I mentioned in the previous reply-
Also, please ensure that in the Table Visual you are using the DimDate instead of the Fact Date.