The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
Hi,
I've a simple model, Iput a pbix file https://1drv.ms/u/s!Amd7BXzYs7AVhBEkwk7KkWhVqete?e=sireeV
I'm trying to calculate runnig total till last month between two selected dates, as example:
Year | month | day | time | running total( classic) | Prior till last month (expected) |
2019 | 1 | 12 | 12 | 12 | 0 |
2019 | 1 | 14 | 15 | 27 | 12 |
2019 | 2 | 12 | 18 | 45 | 27 |
2019 | 2 | 13 | 21 | 66 | 45 |
2019 | 2 | 14 | 24 | 90 | 66 |
2019 | 5 | 15 | 27 | 117 | 90 |
2019 | 5 | 16 | 30 | 147 | 117 |
2019 | 5 | 17 | 33 | 180 | 147 |
2019 | 12 | 18 | 36 | 216 | 180 |
2020 | 4 | 19 | 39 | 255 | 216 |
2020 | 4 | 20 | 42 | 297 | 255 |
2020 | 4 | 21 | 45 | 342 | 297 |
2020 | 5 | 22 | 48 | 390 | 342 |
2020 | 5 | 23 | 51 | 441 | 390 |
2020 | 7 | 24 | 54 | 495 | 441 |
2020 | 8 | 25 | 57 | 552 | 495 |
2020 | 9 | 26 | 60 | 612 | 552 |
2020 | 12 | 27 | 63 | 675 | 612 |
I tried, but prior to date is too slowly, how to optimize it? When I do a search on net, may be I need to use RANKX but I don't to figure out how to use it?
HI @Anonymous,
Have many records your calendar table stored? Account to your description, It seems like you use the cumulative calculation expression on the calendar table to summary table2 records.
For this scenario, it means each calendar row will be looping with table 2 records. (the total looping row count = calendar row count * [2 * selected calendar row count + table2 row count ] )
In addition, I modify the formula to reduce the calculation row counts, you can also try to use the following formula if helps:
prior to date =
VAR currDate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
SUM ( 'Table (2)'[Time ] ),
FILTER ( ALLSELECTED ( 'Table (2)' ), 'Table (2)'[Date] <= currDate )
) + 0
Reference link about Dax iterator performance:
Optimizing nested iterators in DAX - SQLBI
Regards,
Xiaoxin Sheng
@Anonymous , I tried like this
Measure = calculate(sum('Table (2)'[Time ]), filter(ALLSELECTED('Date'), 'Date'[Date]<=eomonth(max('Date'[Date]),-1)))
Data is very small, I do not face any performance issue
what you showing is not last month till date
The file is attached after signature
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
21 | |
20 | |
11 | |
10 | |
7 |