Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I've a data set as under:
ID | FISCAL_YEAR | FISCAL_WEEK |
1 | 2020 | 53 |
1 | 2021 | 1 |
1 | 2021 | 5 |
2 | 2019 | 52 |
3 | 2020 | 9 |
2 | 2018 | 28 |
3 | 2020 | 53 |
4 | 2021 | 1 |
5 | 2021 | 5 |
2 | 2019 | 52 |
5 | 2020 | 53 |
2 | 2021 | 1 |
I want to create a measure to plot on a clustered column with one bar pair showing Sales(Counts) by a selected ID (this ID would be picked using a slicer) for this year (until current week number) vs the average number of sales by ID for this year (until current week number)
And the second pair of bars would represent the same information but for last year(until the same week number as today)
Solved! Go to Solution.
Hi @dollarvora ,
Please refer to my .pbix file.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@dollarvora , First of all create a column Year Week. Given Below.
Year Week = [Year]*100 +[Week]
Create a Date /Week Table
Date = Summarize(Table, [Year], [Week],[Year week])
Join both on Year Week. These are a formula for Year till week
YTD= CALCULATE(Avergae('Table'[ID]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))
LYTD = CALCULATE(Avergae('Table'[ID]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] <= Max('Date'[Week])))
Hi @amitchandak ,
This is really helpful, thanks
But, as I mentioned in the original question I have a slicer that selects the ID, I'd want 2 pairs of bar charts side by side
1st one: Comparing the YTD sales for that selected ID v/s YTD vs the average number of sales by ID for this year
2nd one: same but for the lasy YTD
Thanks again
Hi @dollarvora ,
Please refer to my .pbix file.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |