How to loop dates descending and checking if at least one value is present

[JanSlanina]

Hello everybody,

can somebody help me with this issue, I am struggling with this for few days already and I decided to register and ask for help directly to Power BI community.

The issue is following: I need to present the most recent data in our SQL database. There are logs of statuses from Hydra. I need to report just the KPI breakdowns. Everything was fine, until Monday and we had production in Saturday and we had technical breakdown. My visualisation doesnt show the Saturday data but filters it on previous day by dynamicaly last 1 day, excluding actual day. Basic stuff in visual filter. My boss wasnt really happy, that he isnt able to see weekend. I cant do it by filter I am using because some Saturday we might produce, but most will not. 

The table above is part of what we are using and you can see there is Date and Status, I tried to check by dax like this:

Latest Breakdown date = CALCULATE(MAX(Breakdown[Date]) -1,

                        ALL(Breakdown),

                        Breakdown[status_text] = "NIE JE BALENIE"

                        || Breakdown[status_text] = "TECHNICKÝ PRESTOJ"

                        || Breakdown[status_text] = "KVALITATÍVNY PRESTOJ"

                        || Breakdown[status_text] = "ZMENA VÝROBY"

                        || Breakdown[status_text] = "KVALITAT. PRESTOJ"

                        || Breakdown[status_text] = "TECHNOLOG. PRESTOJ"

                        || Breakdown[status_text] = "NIE JE MATERIÁL"

                        || Breakdown[status_text] = "NASTAVOVANIE"

                        )

But I am too unskilled and dont know how to put this in LOOP so it checks actual date -1 and in that day any of the statuses has log, if not date -1 again and checks again and so on, until it finds day with at least one breakdown and writes f.e. "Last workday" in all the logs from that day from which I can filter in the cards.

can somebody help me with this, please ?
P.S.: Sorry for my english 🙂

[amitchandak]

@JanSlanina , not very clear. For Lat working day and Last day data we can use date table and time intelligence

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

Power BI Workday vs Last Workday- https://youtu.be/MkYLT_GYIbM

This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))

Last Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))

Last Day = CALCULATE(sum('Table'[Qty]), previousday('Date'[Date]))

[JanSlanina]

Sorry for the confusion. 

What I mean by this problem is, that I need to get the last day (except actual day) which has either of those mentioned status texts, for example in new calculated column.
Example: 
Checking dates descending
Today is 19.7.2023 (ignore actual day)
Yesterday 18.7.2023 check for any value in "status_text" = "NIE JE BALENIE"  || "ZMENA VYROBY" || etc... (if no status in this date, continue)
17.7.2023 check for any value in "status_text" = "NIE JE BALENIE"  || "ZMENA VYROBY" || etc... (if no status in this date, continue)
16.7.2023 check for any value in "status_text" = "NIE JE BALENIE"  || "ZMENA VYROBY" || etc... (if no status in this date, continue)
15.7.2023 check for any value in "status_text" = "NIE JE BALENIE"  || "ZMENA VYROBY" || etc... (FOUND STATUS "NIE JE BALENIE", write "Last workday" in the new column)

[Anonymous · Accepted solution]

Hi @JanSlanina ,

Please try:

Last Workday =
VAR LatestDate =
    MAX ( Breakdown[Date] )
VAR CurrentDate = Breakdown[Date]
VAR CurrentStatus = Breakdown[status_text]
VAR LastWorkdayDate =
    CALCULATE (
        MAX ( Breakdown[Date] ),
        FILTER (
            ALL ( Breakdown ),
            Breakdown[Date] < LatestDate
                && ( Breakdown[status_text] = "NIE JE BALENIE"
                || Breakdown[status_text] = "TECHNICKÝ PRESTOJ"
                || Breakdown[status_text] = "KVALITATÍVNY PRESTOJ"
                || Breakdown[status_text] = "ZMENA VÝROBY"
                || Breakdown[status_text] = "KVALITAT. PRESTOJ"
                || Breakdown[status_text] = "TECHNOLOG. PRESTOJ"
                || Breakdown[status_text] = "NIE JE MATERIÁL"
                || Breakdown[status_text] = "NASTAVOVANIE" )
        )
    )
VAR Result =
    IF (
        CurrentDate = LastWorkdayDate
            && CurrentStatus
            IN {
            "NIE JE BALENIE",
            "TECHNICKÝ PRESTOJ",
            "KVALITATÍVNY PRESTOJ",
            "ZMENA VÝROBY",
            "KVALITAT. PRESTOJ",
            "TECHNOLOG. PRESTOJ",
            "NIE JE MATERIÁL",
            "NASTAVOVANIE"
        },
        "Last workday",
        BLANK ()
    )
RETURN
    Result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[JanSlanina]

Hello mate !

Looks like it works ! We produced in Saturday, 22.7.2023 and it filtered all the statuses from that day which are in the code. Thats lovely. 

Thank you so much, I will keep an eye in different situations and data logs and if anything odd occurs I will give you response, for now, I can go ahead and Accept it as solution mate. 🙂  

[JanSlanina]

Hello !

thanks ! I added this as a new column, I will check it on monday if it works. Interestingly, similar code was suggested by Chat GPT after many and many trial and errors with it. 

I will give you more info on Monday after the data will get updated !