Forum Discussion

gmasta1129's avatar
gmasta1129
Icon for Resolver I rankResolver I
10 months ago
Solved

Help with Power BI Formula to ignore weekend dates

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...
Need Help
  • gmasta1129's avatar
    gmasta1129
    10 months ago

    Hello jgeddes ,

     

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

jgeddes's avatar
jgeddes
Icon for Super User rankSuper User
10 months ago

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.