Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am struggling with what seems like an easy thing to do.
I have two measures – “Amount This year” and “Amount Last Year” (using SAMEPERIODLASTYEAR)
I plot these two measures on a line graph months 1 to 12 – if I involve the year, the lines do not overlap, which is what I want so I am only using month number.
However the user can select year 2020, in which case I need February (2) to be the first month on left, then January (1), then December (12) etc. This is a rolling 12, so latest month should be on left, in this case, February.
So I created a disconnected table below and some formula to force an order per the “order” column which gives me the correct order – here February is the latest month so appears first on the left with order amount “0”, then January (0.01) etc
All seems fine – except the user can select 2019 year, in which case I no longer want the below to apply – I just want January through to December as normal.
Can anyone help please?
Basically I need the order to be bespoke for 2020 with current month first on left but normal for any older year.
Thanks
Month Current Month order
2 2 0.00
1 2 0.01
12 2 0.08
11 2 0.09
10 2 0.10
9 2 0.11
8 2 0.13
7 2 0.14
6 2 0.17
5 2 0.20
4 2 0.25
3 2 0.33
what are your measure definitions? I think it can be achieved if you show the last 12 months relative to the time selection, and just order with regular calendar order, you just need to adjust the measures so that CY and LY would show for Feb 2020 and not Feb 2020 and Feb 2019 (which, to my understanding, is the case right now)
Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).
Column1 | Column2 |
A | 1 |
B | 2.5 |
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |