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,
hope to get help for the following problem.
I have a two tables which includes a date, uniqueDevices and activeDevices, there is also a dedicated date table. I want to get a cumulative Total of the activeDevices. The problem I have, for some Countries the activeDevices are the same for couple of weeks and it´s showing Null values in the graph as well as in the table:
Week_Year | #Unique Devices | ActiveDevices_rollingE |
2018-4 | 16 | 32 |
2018-5 | 20 | 35 |
2018-6 | 14 | |
2018-7 | 10 | |
2018-8 | 7 | |
2018-9 | 9 | |
2018-10 | 15 | 37 |
2018-11 | 8 | 38 |
2018-12 | 22 | 66 |
2018-13 | 19 | 76 |
2018-14 | 8 | 88 |
The formula I´m using is:
ActiveDevices_rollingE = CALCULATE (
SUMX(MeasuresTableAirwatch; MeasuresTableAirwatch[ActiveDevices]);
FILTER(ALL(Period[date]);
Period[Date] <= MAX (Airwatch[AppEnrollmentDate])))
I need to have 35 active Devices also in line 2018-6, 2018-7, 2018-8.
Thanks in advance
Best
Lina
Solved! Go to Solution.
Yes, try this instead. (without deleting the relationship)
ActiveDevices_rollingE = var endDate = CALCULATE( LASTDATE(Airwatch[AppEnrollmentDate]), ALL('Airwatch') ) RETURN CALCULATE ( SUM(MeasuresTableAirwatch[ActiveDevices]); ALL(Period[date]); Period[Date] <= endDate )
First off, i've optimised your calculated column:
ActiveDevices_rollingE = var endDate = LASTDATE(Airwatch[AppEnrollmentDate]) RETURN CALCULATE ( SUM(MeasuresTableAirwatch[ActiveDevices]); ALL(Period[date]); Period[Date] <= endDate )
Doing this has made me notice that you are getting the last date from the Airwatch table, if you have a table relationship with this you could be fall foul of the context of that join. I would reconsider that portion next.
Thanks for your answer, the result is the same (missing values for the three weeks). If I delete the relationship between the date table and the AppEnrollmentDate I get the value (88 ActiveDevices) of the last AppEnrollmentDate for all previous weeks. Any other suggestions to solve the problem? Thanks Lina
Yes, try this instead. (without deleting the relationship)
ActiveDevices_rollingE = var endDate = CALCULATE( LASTDATE(Airwatch[AppEnrollmentDate]), ALL('Airwatch') ) RETURN CALCULATE ( SUM(MeasuresTableAirwatch[ActiveDevices]); ALL(Period[date]); Period[Date] <= endDate )
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 |