Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have been stuck on this issue for ages and would like someone to take a look at it.
Basically, I have a table like this:
Configured | Original Timestamp | users.customers |
1 | 13/02/2018 20:25 | A |
4 | 14/02/2018 20:25 | A |
6 | 15/02/2018 20:25 | B |
12 | 16/02/2018 20:25 | A |
5 | 17/02/2018 20:25 | B |
7 | 18/02/2018 20:25 | E |
25 | 19/02/2018 20:25 | C |
What I am trying to achieve is this:
User.customers | Max Configured per Month | Date |
A | 12 | 16/02/2018 |
B | 6 | 15/02/2018 |
C | 25 | 19/02/2018 |
At the moment, the dataset is from Jan 2017 till Feb 2018 so I would like to get the maximum of "configured" number every month for each unique customers.
This is what I have so far but it is not working.
Table =
VAR tblMonths =
SUMMARIZE(
ADDCOLUMNS(
CALENDAR("2016-11-01","2018-03-01"),
"Month",DATE(YEAR([original_timestamp]),MONTH([original_timestamp]),1)
),[Month])
RETURN
ADDCOLUMNS(
GENERATE(
VALUES(system_monitored[users.customer]),
tblMonths
)
,"Order Value" ,
CALCULATE(
MAX(system_monitored[configured]),
FILTER(system_monitored,
system_monitored[users.customer] = EARLIER(system_monitored[users.customer]) && system_monitored[original_timestamp] = EARLIER([Month])
)
))
Solved! Go to Solution.
Hi @okiedokie2017,
If I understand you correctly, you should be able to follow steps below to get the expected result in your scenario.
1. Use the formula below to add a YearMonth column in table 'system_monitored'.
YearMonth = YEAR(system_monitored[Original Timestamp])*100+MONTH(system_monitored[Original Timestamp])
2. Then you should be able to use the formula below to create the expected table.
Table = SUMMARIZE ( system_monitored, system_monitored[YearMonth], system_monitored[users.customers ], "Max Configured per Month", CALCULATE ( MAX ( system_monitored[Configured] ) ), "Date", VAR abc = CALCULATE ( MAX ( system_monitored[Configured] ) ) RETURN CALCULATE ( MAX ( system_monitored[Original Timestamp] ), system_monitored[Configured] = abc ) )
Here is the sample pbix file for your reference.
Regards
Hi @okiedokie2017,
If I understand you correctly, you should be able to follow steps below to get the expected result in your scenario.
1. Use the formula below to add a YearMonth column in table 'system_monitored'.
YearMonth = YEAR(system_monitored[Original Timestamp])*100+MONTH(system_monitored[Original Timestamp])
2. Then you should be able to use the formula below to create the expected table.
Table = SUMMARIZE ( system_monitored, system_monitored[YearMonth], system_monitored[users.customers ], "Max Configured per Month", CALCULATE ( MAX ( system_monitored[Configured] ) ), "Date", VAR abc = CALCULATE ( MAX ( system_monitored[Configured] ) ) RETURN CALCULATE ( MAX ( system_monitored[Original Timestamp] ), system_monitored[Configured] = abc ) )
Here is the sample pbix file for your reference.
Regards
WOw thanks alot.
This is perfect
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |