Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
User | Count |
---|---|
78 | |
75 | |
62 | |
60 | |
47 |
User | Count |
---|---|
109 | |
95 | |
86 | |
79 | |
61 |