Forum Discussion

treatas's avatar
treatas
Regular Visitor
2 years ago

Need Help for Revenue Calculation Considering Working Days and Holidays by Country

Hi everyone,

I'm working on a DAX measure in Power BI and could use some expert advice. My goal is to calculate the average daily revenue adjusted for working days and holidays, which vary by country. To help me do my calculation, I brought the "Country" information in the fact table, and I have a Holidays table that I use to obtain the number of holidays.

Here is a snapshot of the model:

My idea is to use SUMX to interate each row of the invoice table. Then, at each iteration, use a CALCULATE function to evaluate, for the current row, the revenue divided by the number of working days specific to the row's associated country.

And that's where I am lost! I have tried to look at it in various ways, but no luck so far. Here is what I thought would work, but I think I am missing something:

 

SUMX(
    Invoices,
    VAR CurrentRev = SUM(Invoices[Rev])
    VAR CurrentCountry = VALUES(Invoices[Sales_Country])
    VAR HolidayCount = CALCULATE(
        SUM(Holidays[Days count]),
        TREATAS(CurrentCountry, Holidays[Country])
    )
    VAR WorkingDaysCount = CALCULATE(
        COUNTROWS('Date'),
        'Date'[Day of Week Number] <> 6,
        'Date'[Day of Week Number] <> 7,
        'Date'[CurrDayOffset] < 0
    )
    VAR AdjustedWorkingDay = WorkingDaysCount - HolidayCount
    VAR AverageDailySales = DIVIDE(CurrentRev, AdjustedWorkingDay)
    RETURN AverageDailySales
)

 

Any help would be great really

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi treatas ,

    If it is convenient for you, can you give me a sample sample data, so that it can better solve the problem for you, thank you.

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.