How can I show % Change of Revenue Data using Year & Month data?

[ERing]

I have very simple data (like below, sample data attached) where I have 3 years of revenue data by state and columns for year and month. My requirements are:

1. User can select a current and comparison period using slicers (either two slicers if mo & year are combined or four slicers if mo and year are seperate).

2. Visual to show Revenue by state for selected and comparison period.

3. Visual to show % change by state for current vs comparison period.

I'm used to working with data where I have a specific date for each data point and I'm struggling with how to modify my approach to work with month/year data.

Any thoughts on how I can produce the requried visuals?

Month	Year	State	Revenue
1	2022	FL	915647
2	2022	UT	1218951
3	2022	AZ	1340223
4	2022	NV	2354622
5	2022	CA	601569
6	2022	NC	854430
7	2022	SC	1867108
8	2022	TN	1551360
9	2022	AL	2305461
10	2022	GA	705214
11	2022	WY	2102439
12	2022	IA	1307902
1	2023	FL	504700
2	2023	UT	677099
3	2023	AZ	1076230
4	2023	NV	557247
5	2023	CA	1388999
6	2023	NC	1689891
7	2023	SC	500377
8	2023	TN	1173848
9	2023	AL	1927590
10	2023	GA	1344214
11	2023	WY	1456994
12	2023	IA	2175240
1	2024	FL	2122227
2	2024	UT	863611
3	2024	AZ	2499410
4	2024	NV	1452952
5	2024	CA	2204080
6	2024	NC	1066495
7	2024	SC	1686869
8	2024	TN	2056826
9	2024	AL	2480275
10	2024	GA	2189260
11	2024	WY	686364
12	2024	IA	1655190

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached PBI file.  Please review it and apply it to your dataset.

Hope this helps.

[ERing]

Thanks @Ashish_Mathur Will try this also.

[Daniel29195 · Accepted solution]

@ERing 

output 

model : 

steps : 

create the 2 tables  :  currentperiod  and previousperiod as follow : 

currentperiod =

ALLNOBLANKROW(data[Month], data[Year])

previousperiod =

ALLNOBLANKROW(data[Month], data[Year])

step2 

then create a calculated column ,  year-month in the  3 tables : 

year-month = 'currentperiod'[Year] & "-" & 'currentperiod'[Month]

step3 : 

create 2 slicers, reading from currentperiod and previousperiod respectivtly.

finally, create the following measures : 

period1 sales =

SUM(data[Revenue])

period2 sales =

CALCULATE(

SUM(data[Revenue]),

REMOVEFILTERS(currentperiod),

USERELATIONSHIP(currentperiod[year-month],'previousperiod'[year-month])

)

diff =

[period1 sales]  - [period2 sales]

% variance =

[diff]  /  [period2 sales]

le me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[ERing]

@Daniel29195 Works perfectly! Thank you!

[lbendlin]

No need for slicers, you can do all this directly in the Matrix visual

The new Visual Calculations make this very convenient.  

You can show by year or by month,  you can pick any two months etc.  All in the visual.

[ERing]

Thanks @lbendlin I will try this method as well! I'll follow up after i've tried it.