Help with Power BI Formula to ignore weekend dates

[gmasta1129]

Hello, 

I created a measure in Power BI to calculate the difference between sales from one day to the next.  The sales report runs Mon to Fri only. Therefore I need the formula to subtract Monday's sales value from Friday's sales value.  Can someone please help with the formula below to ignore weekend dates. 

Sales Difference =

VAR CurrentDate = MAX('Sales Report'[Run Date])

VAR CurrentFacility = MAX('Sales Report'[facility code])

VAR CurrentSales =

    CALCULATE(

        SUM('Sales Report'[daily sales]),

        'Sales Report'[facility code] = CurrentFacility,

        'Sales Report'[Run Date] = CurrentDate

    )

VAR PrevDate =

    CALCULATE(

        MAX('Sales Report'[Run Date]),

        FILTER(

            ALL('Sales Report'),

            'Sales Report'[facility code] = CurrentFacility &&

            'Sales Report'[Run Date] < CurrentDate

        )

    )

VAR PrevSales =

    CALCULATE(

        SUM(Sales Report[daily sales]),

        'Sales Report'[facility code] = CurrentFacility,

        'Sales Report'[Run Date] = PrevDate

    )

RETURN

    IF(

        ISBLANK(PrevSales),

        BLANK(),

        CurrentSales - PrevSales

    )

[v-venuppu]

Hi @gmasta1129 ,

Great to hear that it's working from your end! Could you please share the solution? It would be really helpful for others in the community who might be facing similar issues and can address them quickly. Also, I would suggest accepting your approach as the solution so that it can benefit others as well.

Thank you.

[v-venuppu]

Hi @gmasta1129 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @gmasta1129 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @jgeddes for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[gmasta1129 · Accepted solution]

Hello @jgeddes ,

Unfortunately, the below did not work for me. I figured out another formula to use which worked.  

[jgeddes]

Here is one way to do this in DAX. (There are probably better ways...)
Consider sales data...

I get the final result of...

Using three measures.

Sales (Weekday Only) = 
var _vTable = 
SUMMARIZE(
    financials,
    financials[Date],
    "__sales", IF(NOT(WEEKDAY(financials[Date]) IN {1,7}), SUMX(financials, financials[Sales]), BLANK())
)
RETURN
SUMX(_vTable,[__sales])

Sales (Previous Workday) = 
var _vTable =
SUMMARIZE(
    financials,
    financials[Date],
    "__sales", 
    IF(
        NOT(WEEKDAY(financials[Date]) IN {1,7}), 
        SUMX(
            FILTER(
                ALL(financials), 
                financials[Date] = MAXX(
                                    FILTER(ALL(financials), (NOT WEEKDAY(financials[Date]) IN {1,7}) && financials[Date] < SELECTEDVALUE(financials[Date])), 
                                    financials[Date]
                                )
            ), 
            financials[Sales]
        ), 
        BLANK()
    ) 
)
RETURN
SUMX(_vTable,[__sales])

Sales Delta From Previous Weekday = 
[Sales (Weekday Only)] - [Sales (Previous Workday)]

This methodology will find the sales from the previous day that is not Saturday or Sunday. Meaning it does not rely on Mondays or Fridays at all. 
I have attached the pbix if you want to play with it.