Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a table with IDs per month and a balance. I'm wanting to have a summary of the balance x months ago, but seem to be going around in circles not getting anywhere.
Using the initial table below...
ID | Report Month | Balance |
1 | 01/01/2022 | 1000 |
1 | 01/02/2022 | 1500 |
1 | 01/03/2022 | 2000 |
1 | 01/04/2022 | 1750 |
1 | 01/05/2022 | 2250 |
1 | 01/06/2022 | 2750 |
2 | 01/01/2022 | 700 |
2 | 01/02/2022 | 1400 |
2 | 01/03/2022 | 2100 |
2 | 01/04/2022 | 1900 |
2 | 01/05/2022 | 1850 |
2 | 01/06/2022 | 1950 |
I'd like to end up with...
ID | Report Month | Balance | 1 Month Ago | 6 Months Ago |
1 | 01/01/2022 | 1000 |
|
|
1 | 01/02/2022 | 1500 | 1000 |
|
1 | 01/03/2022 | 2000 | 1500 |
|
1 | 01/04/2022 | 1750 | 2000 |
|
1 | 01/05/2022 | 2250 | 1750 |
|
1 | 01/06/2022 | 2750 | 2250 | 1000 |
2 | 01/01/2022 | 700 |
|
|
2 | 01/02/2022 | 1400 | 700 |
|
2 | 01/03/2022 | 2100 | 1400 |
|
2 | 01/04/2022 | 1900 | 2100 |
|
2 | 01/05/2022 | 1850 | 1900 |
|
2 | 01/06/2022 | 1950 | 1850 | 700 |
I've tried numerous approaches, but so far, nothing has worked. Any advice would be greatly appreciated.
Many thanks
Solved! Go to Solution.
Do you have a dedicated Calendar table?
You should create relationship between the date column you need and the date column on your calendar table.
You can create a Calendar by selecting create table and just typing in Calendar = CALENDARAUTO()
After you've done this create a relationship between the date columns in your modelling view.
You can then use a measure to calculate both.
CALCULATE(SUM([Balance])) will give you the current balance
CALCULATE(SUM([BALANCE]), DATEADD('Calendar'[Date], -6, MONTHS) should give you the previous value.
I'm just typing these off by heart so it might not be 100%, intellisense should get you through though.
Do you have a dedicated Calendar table?
You should create relationship between the date column you need and the date column on your calendar table.
You can create a Calendar by selecting create table and just typing in Calendar = CALENDARAUTO()
After you've done this create a relationship between the date columns in your modelling view.
You can then use a measure to calculate both.
CALCULATE(SUM([Balance])) will give you the current balance
CALCULATE(SUM([BALANCE]), DATEADD('Calendar'[Date], -6, MONTHS) should give you the previous value.
I'm just typing these off by heart so it might not be 100%, intellisense should get you through though.
Hi @satkin ,
You can create a measure as below:-
1 month ago =
CALCULATE (
SUM ( 'Table'[Balance] ),
FILTER (
ALL ( 'Table' ),
MONTH ( 'Table'[Report Month] )
= MONTH ( EOMONTH ( MAX ( 'Table'[Report Month] ), -1 ) )
&& 'Table'[ID] = MAX ( 'Table'[ID] )
)
)
6 month ago =
CALCULATE (
SUM ( 'Table'[Balance] ),
FILTER (
ALL ( 'Table' ),
MONTH ( 'Table'[Report Month] )
= MONTH ( EOMONTH ( MAX ( 'Table'[Report Month] ), -5 ) )
&& 'Table'[ID] = MAX ( 'Table'[ID] )
)
)
Output:-
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |