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 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
Solved! Go to Solution.
@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
@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
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |