Forum Discussion

Sammy1965's avatar
Sammy1965
New Member
1 year ago

If Query in Dax

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än...
Poojara_D12's avatar
Poojara_D12
Super User
1 year ago

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's avatar
Sammy1965
New Member
1 year ago

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's avatar
    Anonymous
    Not applicable
    1 year ago

    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