Grouping Sat-Sun-Mon into Monday

DAX DAX Calculations
1

Hello,

I’m re-doing a Tableau report in Power BI. The report contains a calculation where I want to combine Sat-Sun-Mon revenue into “Monday revenue”… and report that with the typical Tue revenue, Wed rev, Thu rev, and Fri rev.

I’m attaching both the PBIX and further details about the desired outcome, and how it was accomplished in Tableau.

All help is appreciated.

Best regards,
Kevin

Grouping Days.docx (69.9 KB)

RECEIVABLES TEST DASH.pbix (209.2 KB)

2

Hello @kkieger,

Thank you for posting your query onto the Forum.

Firstly, I’d set up a Date Table in your model. Below is the screenshot provided for the reference -

Weekday Sales - 1
Weekday Sales - 11400×544 62.1 KB

And then here’s the formula that I’ve used to calculate the “Total Actual Revenue” for weekly sales.

Total Actual Non Weekend Days = 
VAR WeekendCheck = SELECTEDVALUE( Dates[Day Type] ) = "Weekend"
VAR WorkdayCheck = SELECTEDVALUE( Dates[DayOfWeekName] ) = "Monday"

RETURN
IF( WeekendCheck = TRUE, BLANK(),
    IF( WorkdayCheck = FALSE,
        [Total Actual],
            CALCULATE( [Total Actual],
                FILTER( ALL( Dates ),
                    Dates[Date] > MIN( Dates[Date] ) - 3 && Dates[Date] <= MIN( Dates[Date] ) ) ) ) )

Below is the screenshot of the result provided for the reference -

Weekday Sales - 2
Weekday Sales - 2692×733 135 KB

I’m also attaching the working of the PBIX file for the reference. Also providing the link of a video below where Sam has covered this concept.

Hoping you find this useful and meets your requirements. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

RECEIVABLES TEST DASH.pbix (258.0 KB)

1 Like
3

Hi @kkieger,

Also attaching the working of the Total Forecast, if required.

Weekday Sales - 2
Weekday Sales - 2931×727 171 KB

Thanks and Warm Regards,
Harsh

RECEIVABLES TEST DASH v2.pbix (257.9 KB)

4

I thought of giving a try with Power Query, but after seeing so many calculated columns it was not feasible. the above reply by @Harsh solved your problem. Just small advice try to change your Calculated columns into Measures (columns not used for slicer) as it will be very faster and your model will consume less memory.

5

This is excellent. I’ll mark the issue as complete.

If you have a link or source file for adding the date table used, I’d appreciate you providing it.

Thank you,
Kevin

6

Hello @kkieger,

You’re Welcome. :slightly_smiling_face:

I’m glad I was able to help you.

Sure, below is the link of the Date table code provided which was created by one of our expert @Melissa. It’s available under the category “M Code Showcase”. And you can also find under the Advanced Editor option in the solution file that I’ve provided. Just select the “Query1” and go to the Advanced Editor. Below is the screenshot provided for the reference -

Advanced Editor

Thanks and Warm Regards,
Harsh