Show the last 7 days with X axis as weekdays.

[Learner_SG]

Hi , I have a chart which shows the 7 days data and in the x axis it shows the weekdays(Sun,Mon...). I need to show it in the last 7 day order. ie , if it is Friday today the last day in the chart should be fri and it should dynamically change accordingly.

I have other charts which shows the date in the x axis and when I select the last 7 days in the filter,it works. but not for this case as Sat Sun is in the text pattern. Do let me know the sample data is needed to check?

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

One of ways to sort as you wanted is to create a ranking measure that shows rank of the date of the day, and then insert the measure into the tooltip of the visualization. And then sort the visualization by the ranking measure.

Selected dayname rank =
VAR _today =
    TODAY ()
VAR newtable =
    SUMMARIZE ( 'Calendar', 'Calendar'[Date], 'Calendar'[Day Name] )
VAR rankingtable =
    FILTER (
        ADDCOLUMNS (
            newtable,
            "@lastsevendaysqty", [Last seven days quantity measure:],
            "@ranking",
                CALCULATE (
                    RANKX (
                        DATESINPERIOD ( 'Calendar'[Date], _today, -7, DAY ),
                        CALCULATE ( MAX ( 'Calendar'[Date] ) ),
                        ,
                        ASC
                    )
                )
        ),
        [@lastsevendaysqty] <> BLANK ()
    )
VAR daynameranking =
    SUMMARIZE ( rankingtable, 'Calendar'[Day Name], [@ranking] )
RETURN
    SUMX ( daynameranking, [@ranking] )

[Learner_SG]

@Jihwan_Kim  Thanks a lot for the help I am almost there but got stuck with a small issue. The sorting goes wrong in this case for some days as shown below.

Last time when I faced this issue, what I did was sorting the weekday column using another column with weekday numbers but since this is dynamic, I can't use that option. Any thoughts about it?

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Could you please share your sample pbix file's link here? And then I can try to look into it to come up with a more accurate solution.

thank you.

[Learner_SG]

@Jihwan_Kim 

here is the link.

in the last 7 days data, data from sunday is missing due to server error.

https://drive.google.com/file/d/1SaHsSvCTaasPMOlqIKmgbpEAMiTcGfbf/view?usp=sharing.

Could help me why the error happens for the sort issue as I could not resolve it.Thanks.

[Jihwan_Kim]

Hi,

Thank you very much for your link.

I looked into it but sorry to say that it was quite difficult for me to understand the model.

- A Calendar table is not connected to the table that other measures are calculating.

- The Visualization's X-axis is not coming from a calendar table, and I cannot know how to connect two tables.

[Learner_SG]

@Jihwan_Kim 

I found a workaround and solved it.Thanks a lot for your help.