Forum Discussion

gmasta1129's avatar
gmasta1129
Resolver 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'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
    )
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.  

5 Replies

  • jgeddes's avatar
    jgeddes
    Super 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.

     

  • v-venuppu's avatar
    v-venuppu
    Community Support
    10 months ago

    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's avatar
      gmasta1129
      Resolver I
      10 months ago

      Hello jgeddes ,

       

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

  • v-venuppu's avatar
    v-venuppu
    Community Support
    10 months ago

    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's avatar
    v-venuppu
    Community Support
    10 months ago

    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.