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 am using just one table which has month(01/01/2018 - format), day, Number of Customers fields. I have created Cumulative Last month & Cumulative 2 month Ago measures using the below formulas. I have month field (Jan, Feb, Mar, etc ) as slicer & "Date", "Cumulative Customers"," Cumulative Customers Last Month", "Cumulative Customers 2M Ago" as Area Chart. When I select all months in slicer then values for "Cumulative Customers"," Cumulative Customers Last Month", "Cumulative Customers 2M Ago" are displayed but when I select just one month in slicer values for only "Cumulative Customers" are displayed, but not for " Cumulative Customers Last Month", "Cumulative Customers 2M Ago". My objective is to get results for " Cumulative Customers Last Month", "Cumulative Customers 2M Ago" as well when I select any month in slicer. Not sure what is missing. Any help on this is appreciated. Thanks
Cumulative Customers=
CALCULATE([Total Customers],
FILTER(ALLSELECTED(Table1 ),
Table1[CalendarDate] <= MAX(Table1[CalendarDate])))
Cumulative Customers Last Month=
CALCULATE([Total Customers 1M],
FILTER(ALLSELECTED(Table1 ),
Table1[CalendarDate] <= MAX(Table1[CalendarDate])))
Cumulative Customers 2M Ago=
CALCULATE([Total Customers 2M Ago],
FILTER(ALLSELECTED(Table1 ),
Table1[CalendarDate] <= MAX(Table1[CalendarDate])))
Solved! Go to Solution.
Hi DataUPBI,
As a general solution , modify your measure like pattern below and check if it can meet your requirement:
Cumulative Customers = SWITCH ( CALCULATE ( COUNT ( Table1[CalendarDate] ), FILTER ( ALLSELECTED ( Table1 ), Table1[CalendarDate] <= MAX ( Table1[CalendarDate] ) ) ), 1, [Total Customers], 2, [Total Customers 1M], 3, [Total Customers 2M Ago] )
Regards,
Jimmy Tao
Hi DataUPBI,
As a general solution , modify your measure like pattern below and check if it can meet your requirement:
Cumulative Customers = SWITCH ( CALCULATE ( COUNT ( Table1[CalendarDate] ), FILTER ( ALLSELECTED ( Table1 ), Table1[CalendarDate] <= MAX ( Table1[CalendarDate] ) ) ), 1, [Total Customers], 2, [Total Customers 1M], 3, [Total Customers 2M Ago] )
Regards,
Jimmy Tao
Hi Jimmy,
Thanks for looking into this. I need Cumulative numbers in 3 different fields as Cumulative Customers, Cumulative Customers 1M agao, Cumulative Customers 2M ago.
Regards
Hi DataUPBI,
Could you share some sample data and clarify more details about your requirement?
Regards,
Jimmy Tao
Total customers are calculated using
Total Customers 1M Ago = CALCULATE([Customers], DATEADD(Table1[CalendarDay], -1, MONTH))
Hi DataUPBI,
Have you solved your problem? Could you kindly mark my answer?
Regards,
Jimmy Tao
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |