Forum Discussion
Rolling 12 month based on master calenter selection
Hi All
I have dataset like below table
Reference File : Rolling 12 month (.pbix)
| Month Start Date | # New Client | # Chrun Client | Type of Revenue |
| 01-12-2018 | 2 | Non Recurring | |
| 01-12-2018 | 30 | Recurring | |
| 01-01-2019 | 2 | Non Recurring | |
| 01-01-2019 | 1 | Recurring | |
| 01-02-2019 | 2 | Non Recurring | |
| 01-03-2019 | 3 | Recurring | |
| 01-04-2019 | 1 | Recurring | |
| 01-05-2019 | 4 | 2 | Recurring |
| 01-06-2019 | 3 | Recurring | |
| 01-07-2019 | 1 | Non Recurring | |
| 01-07-2019 | 3 | Recurring | |
| 01-08-2019 | 7 | Non Recurring | |
| 01-08-2019 | 1 | 1 | Recurring |
| 01-09-2019 | 1 | Non Recurring | |
| 01-09-2019 | 11 | 1 | Recurring |
| 01-10-2019 | 2 | Non Recurring | |
| 01-10-2019 | 2 | Recurring | |
| 01-11-2019 | 3 | Non Recurring | |
| 01-11-2019 | 10 | 2 | Recurring |
| 01-12-2019 | 3 | Non Recurring | |
| 01-12-2019 | 11 | 1 | Recurring |
| 01-01-2020 | 5 | 2 | Recurring |
| 01-02-2020 | 1 | Non Recurring | |
| 01-02-2020 | 4 | 2 | Recurring |
| 01-03-2020 | 1 | Recurring |
i want to create two measure
Ending Client & Starting Client
# Starting Client = Previous Month of # Ending Client Value
# Ending Client = (# Starting Client + # New Client) - # Chrun Client
i have created master calendar by using Month start date
i want to show rolling 12 month value based on selected month
if i selected Month Filter value is Mar-2020 and Type of Revenue = Recurring i want to show rolling 12 month value like below image
Anonymous
We have made it a cumulative total, As there is no last month value.
Cumm Last month (Starting Client )= CALCULATE(SUMX(Table,Table[# New Client]-Table[# Chrun Client]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,month))))
Cumm This Month (Ending Client )= CALCULATE(SUMX(Table,Table[# New Client]-Table[# Chrun Client]),filter(date,date[date] <=maxx(date,date[date])))
Did not check pbix. Will do if this does not help
1 Reply
- amitchandak
Super User
Anonymous
We have made it a cumulative total, As there is no last month value.
Cumm Last month (Starting Client )= CALCULATE(SUMX(Table,Table[# New Client]-Table[# Chrun Client]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,month))))
Cumm This Month (Ending Client )= CALCULATE(SUMX(Table,Table[# New Client]-Table[# Chrun Client]),filter(date,date[date] <=maxx(date,date[date])))
Did not check pbix. Will do if this does not help