Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
anupindi_lavany
New Member

Compare day on day per week

In my power bi dataset, I have an orders table with order id column. I want to show a report where in I compare the total orders per day of last week monday to this week monday, tuesday to tuesday so on for the entire week. I was able to achive the cumulative order count for week on week comparison with relative week i.e present week being 0 and last week being -1. But I want to further drill down and show day on day of last week vs current week. I am attaching a visual below with what I want to achive.

anupindi_lavany_0-1692165936235.png

I used the dax 

LastWeekOrderCount =
CALCULATE(
    COUNT('Orders'[orderid]),
    'Orders'[RelativeWeekNumber] = -1
) to calculate last week order count and 
ThisWeekOrderCount =
CALCULATE(
    COUNT('Orders'[orderid]),
    'Orders'[RelativeWeekNumber] = 0
) by using the relative week number column which I derived with extract week function in sql. 
I also have total count per day but I want to plot it as a comparison between two bars as I mentioned in the above pic, how can I achive it ?  Also in terms of choosing a visual and plotting it which one is suitable?
@amitchandak @Ashish_Mathur 
2 REPLIES 2
mickey64
Super User
Super User

I made a calendar table.

 

Step 1: I made a calendar table.

mickey64_2-1692171305545.png

 

mickey64_3-1692171371947.png

 

mickey64_4-1692171401717.png

 

mickey64_5-1692171427300.png

Year hierarchy

mickey64_6-1692171469777.png

 

Step 2: I made a relationship.

mickey64_1-1692171257385.png

 

Step 3: I made some visuals.

mickey64_7-1692172132678.png

 

Step 4: I did drilldown.

mickey64_9-1692172231829.png

mickey64_8-1692172204536.png

 

As my Power BI version is Japanese, days of week are Japanese.

I think when your Power BI version is English, days of week may be English.

Thank you, I was able to achive the visuals in the same way. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.