If Query in Dax

[Sammy1965]

I have created a live dashboard where I query the posting date of an order in a field using the Posting Date field.

For today

Warenausgänge heute = CALCULATE(
COUNT('Archiv_Gebuchte_Warenausgänge'[No_]),
'Archiv_Gebuchte_Warenausgänge'[Posting Date]=TODAY()

For yesterday

Warenausgänge gestern1 = CALCULATE(
COUNT('Archiv_Gebuchte_Warenausgänge'[No_]),
'Archiv_Gebuchte_Warenausgänge'[Posting Date]=PREVOUSDAY()

The problem is that when I look at my dashboard on Monday, the field only shows ‘empty’ because no work was done on Sunday or Saturday.

How do I design the DAX query so that it checks the previous day and 2 previous days to see if they are empty. Because sometimes there is also work on Saturday.

How do I query the above-mentioned measures to check whether the [Posting Date] field is filled on the previous day and 2 days before?

If the previous day does not exist, then take 2 days before, if 2 days before nothing exists, then take 3 days before

Translated with DeepL.com (free version)

[Poojara_D12]

Hi @Sammy1965 

To design a DAX query that checks the previous day, two days before, and three days before in case the previous day has no data, you can use a combination of IF, OR, and CALCULATE functions along with DATEADD to evaluate the dates sequentially.

Approach:

• First, check the previous day (Yesterday).
• If no data exists, check two days before (two days ago).
• If two days before has no data, check three days before (three days ago).

Here’s a possible solution using DAX:

Measure for Orders (Work Done) on Previous Days:

Warenausgänge letze3Tage = 
VAR Yesterday = CALCULATE(COUNT('Archiv_Gebuchte_Warenausgänge'[No_]), 'Archiv_Gebuchte_Warenausgänge'[Posting Date] = PREVIOUSDAY(TODAY()))
VAR TwoDaysAgo = CALCULATE(COUNT('Archiv_Gebuchte_Warenausgänge'[No_]), 'Archiv_Gebuchte_Warenausgänge'[Posting Date] = DATEADD(TODAY(), -2, DAY))
VAR ThreeDaysAgo = CALCULATE(COUNT('Archiv_Gebuchte_Warenausgänge'[No_]), 'Archiv_Gebuchte_Warenausgänge'[Posting Date] = DATEADD(TODAY(), -3, DAY))

RETURN 
    IF(Yesterday > 0, Yesterday, 
        IF(TwoDaysAgo > 0, TwoDaysAgo, 
            IF(ThreeDaysAgo > 0, ThreeDaysAgo, 0)))

If your week starts on Monday and you want the measure to handle the weekend scenario properly (like checking Saturday or Friday if Sunday has no data), you might need to adjust the logic slightly based on the exact requirement, but this approach should work to look backward through the most recent dates with data.

Summary:

• The measure checks yesterday first.
• If no data is found for yesterday, it checks two days ago, then three days ago.
• Returns 0 if no data is found for any of the days.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[Sammy1965]

Hello Poojara_D12,

thank you very much for your quick reply. And thank you very much for your help.
DAX is still absolutely new to me and I have only been working with Power BI for a few weeks... (I love it)

But I have an error message when i try to create the measure

A function of type ‘PREVIOUSDAY’ was used in a true/false expression that serves as a table filter expression. This is not permitted.

The original message in German is:
Eine Funktion vom Typ 'PREVIOUSDAY' wurde in einem True/False-Ausdruck verwendet, der als Tabellenfilterausdruck dient. Dies ist nicht zulässig.

[Anonymous]

Hi @Sammy1965 ,

Base on your description, it seems like you want to the count of No_ which the date is before today. I created a sample pbix file(see the attachment), please check if that is what you want.  You can update the formla of measure

[Warenausgänge gestern1] as below:

Warenausgänge gestern1 = 
VAR _predate =
    CALCULATE (
        MAX ( 'Archiv_Gebuchte_Warenausgänge'[Posting Date] ),
        FILTER (
            ALLSELECTED ( 'Archiv_Gebuchte_Warenausgänge' ),
            'Archiv_Gebuchte_Warenausgänge'[Posting Date] < TODAY ()
        )
    )
RETURN
    CALCULATE (
        COUNT ( 'Archiv_Gebuchte_Warenausgänge'[No_] ),
        FILTER (
            ALLSELECTED ( 'Archiv_Gebuchte_Warenausgänge' ),
            'Archiv_Gebuchte_Warenausgänge'[Posting Date] = _predate
        )
    )

Best Regards

[Sammy1965]

Hello,

i want the value fron FRiday at monday, because at saturday  and sunday ihave no values.
From Time to time the Storage is working at Saturaday an Sunday.
In this case i need the value from the deay before.....

So I always need the value of the last day in which values are available.
If the weekend was not worked, I need the value from Friday or the last active day before today.
I always need the value of the last active day before today.....

[Anonymous]

Hi @Sammy1965 ,

The formula I provided in my previous post is designed to first identify the maximum date before today where the value is not blank, and then retrieve the value for that date. Could you please let me know if this solution is not working in your fact table? If possible, kindly share some sample data (excluding any sensitive information) along with the expected result based on the provided sample data. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

Best Regards

[johnt75]

You could create a measure like

Prev Day =
VAR ValidDates =
    CALCULATETABLE (
        DISTINCT ( 'Archiv_Gebuchte_Warenausgänge'[Posting Date] ),
        'Archiv_Gebuchte_Warenausgänge'[Posting Date] < TODAY (),
        REMOVEFILTERS ( 'Date' )
    )
VAR PrevDay =
    INDEX (
        1,
        ValidDates,
        ORDERBY ( 'Archiv_Gebuchte_Warenausgänge'[Posting Date], DESC )
    )
RETURN
    PrevDay

and a similar measure for the 2nd previous day by changing the 1 to 2.

Once you have the measures you should be able to use them as filters in any calculations or visuals you need.