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.
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!
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |