Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
I have a document table which contains about 25 000 documents. This table contains columns for planned issue dates and actual issue dates.
My goal is to build a line chart with one line for number of planned document issues and one line for number of actual documents issued (sliceable on discipline).
This is what I have done so far:
1. Made separate date table
2. Set relationships between original document table and date table (planned issue date & date) (active)
3. Set relationships between original document table and date table (actual issue date & date) (inactive)
4. Made measure for count of planned issues per day in date table (CALCULATE/COUNTA/ALLSELECTED)
5. Made measure for count of actual issues per day in date table (CALCULATE/COUNTA/USERELATIONSHIP/ALLSELECTED)
6. Made measure for running total of the planned issues in step 4 (CALCULATE/SUMX/FILTER/ALLSELECTED/ISONORAFTER)
7. Made measure for running total of the actual issues in step 5 (CALCULATE/SUMX/USERELATIONSHIP/FILTER/ALLSELECTED/ISONORAFTER)
The problem is that I cannot get my running total actual curve (step 7) to end at today(). If sentence involving today() and blank () will not do the trick.
Any suggestions?
Solved! Go to Solution.
hi, @jsha
After my research, you may try to improve your formula as below:
actual = var _maxdate=IF(CALCULATE(MAX(Table1[Date]))<=TODAY(),MAX(Table1[Date]),BLANK()) return CALCULATE(SUMX(Table1,[Sales]),FILTER(ALLSELECTED(Table1[Date]),ISONORAFTER(Table1[Date],_maxdate,DESC)))
Result:
Best Regards,
Lin
hi, @jsha
After my research, you may try to improve your formula as below:
actual = var _maxdate=IF(CALCULATE(MAX(Table1[Date]))<=TODAY(),MAX(Table1[Date]),BLANK()) return CALCULATE(SUMX(Table1,[Sales]),FILTER(ALLSELECTED(Table1[Date]),ISONORAFTER(Table1[Date],_maxdate,DESC)))
Result:
Best Regards,
Lin
Excellent. Thank you!
User | Count |
---|---|
91 | |
77 | |
71 | |
64 | |
58 |
User | Count |
---|---|
106 | |
97 | |
76 | |
63 | |
60 |