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 checkd all options of Cumulative Count I have found there and none of them worked out.
I have a table of accounts that we work with. Some of them are ok and some of them are in the risk of leaving or were in it.
The Fields are:
Account Id
CreatedOn (the date when the account started to work)
ChangedToRiskOn (the date when it was moved to the status "In the risk of leaving")
Cleint Status (if he is an Active Client, inActive or Temporary Blocked)
AccountId CreatedOn ChangedToRiskOn ClientStatus 1 01.01.2017 03.03.2018 Active 2 02.02.2017 12.11.2017 Active 3 03.03.2017 09.08.2017 inActive 4 04.04.2017 TempBl 5 02.02.2018 08.10.2018 Active 6 03.04.2018 12.09.2018 Active 7 04.05.2018 Active 8 07.05.2018 15.06.2018 TempBl 9 06.06.2018 inActive 10 11.07.2018 03.09.2018 inActive 11 03.10.2018 TempBl 12 09.11.2018 Active 13 11.12.2018 25.12.2018 Active 14 13.12.2018 Active 15 25.12.2018 Active
I want to make a GRAPH with Count and Cumulative Count by MONTH of the Active Clients which are in the Risk of Leaving for the last 10 Months. That means that the date will change dynamically with the beginning of every new month.
So it should I am supposed to get 2 data rows:
Month Count Cumulative Count March 1 1 April 0 1 May 0 1 June 0 1 July 0 1 August 0 1 September 1 2 October 1 3 November 0 3 December 1 4
The issue is when I try to do cumulative total, it takes all data, not for the selected 10 Months. So if there is any Active Client in Risk of Leaving in 2017, he will calculate it and so the 1st cumulative figure in the chosen date fram will not be 1 but will include the figures from 2017.
Another option I tried simply puts 4 in every row.
I tried to do it with Measure and Column, adding the Date Table and without.
It doesn't work.
Can you please help me with the solution?
Thank you!
Solved! Go to Solution.
Hi @JuliaGumina,
New a calendar table. Select [MonthName] column, change its "Sort By column" from default to [MonthNo]. And establish a one to many relationship between 'DateDim' and 'ClientStatus Table'.
DateDim = ADDCOLUMNS ( CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2018, 12, 31 ) ), "MonthNo", MONTH ( [Date] ), "MonthName", FORMAT ( [Date], "MMMM" ) )
Create measures:
count = CALCULATE ( COUNT ( 'ClientStatus Table'[AccountId] ), FILTER ( 'ClientStatus Table', 'ClientStatus Table'[ClientStatus] = "Active" ) ) + 0 Cumulative Count = SUMX ( FILTER ( ALLSELECTED ( DateDim ), DateDim[MonthNo] <= MAX ( DateDim[MonthNo] ) ), [count] )
Add [MonthName] from 'DateDim' and above two measures into table visual. Apply "Relative Date filtering" in "Visual level filters", as shown in below screenshot.
Best regards,
Yuliana Gu
Hi @JuliaGumina,
New a calendar table. Select [MonthName] column, change its "Sort By column" from default to [MonthNo]. And establish a one to many relationship between 'DateDim' and 'ClientStatus Table'.
DateDim = ADDCOLUMNS ( CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2018, 12, 31 ) ), "MonthNo", MONTH ( [Date] ), "MonthName", FORMAT ( [Date], "MMMM" ) )
Create measures:
count = CALCULATE ( COUNT ( 'ClientStatus Table'[AccountId] ), FILTER ( 'ClientStatus Table', 'ClientStatus Table'[ClientStatus] = "Active" ) ) + 0 Cumulative Count = SUMX ( FILTER ( ALLSELECTED ( DateDim ), DateDim[MonthNo] <= MAX ( DateDim[MonthNo] ) ), [count] )
Add [MonthName] from 'DateDim' and above two measures into table visual. Apply "Relative Date filtering" in "Visual level filters", as shown in below screenshot.
Best regards,
Yuliana Gu
Thank you. But if i state the particular first and last dates in Dim Date how can I use it after when I have new clients? In 2019 for instance?
The table is updating.
Thank you!
Hi @JuliaGumina,
DateDim = ADDCOLUMNS ( CALENDAR ( DATE ( YEAR ( MIN ( 'ClientStatus Table'[ChangedToRiskOn] ) ), 1, 1 ), DATE ( YEAR ( MAX ( 'ClientStatus Table'[ChangedToRiskOn] ) ), 12, 31 ) ), "MonthNo", MONTH ( [Date] ), "MonthName", FORMAT ( [Date], "MMMM" ) )
Best regards,
Yuliana Gu
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 | |
102 | |
87 | |
64 |