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 report being created in a direct query mode and have a requirement to calculate a running total between two dates selected by the date slicer. See sample data below,
Col 2 and Col 3 are Calculated Measures
the Measure column is a sum of Col 2 (for a specific date) and Col 3 (running total from start of the date selected through a slicer until that day)
Created Date | Col 1 | Col 2 | Col 3 | Measure |
7/1/2019 | 3073 | 2 | 0 | 2 |
7/2/2019 | 579 | 1 | 2 | 3 |
7/3/2019 | 4952 | 2 | 1 | 5 |
7/4/2019 | 330 | 0 | 2 | 5 |
7/5/2019 | 1920 | 2 | 0 | 7 |
7/6/2019 | 258 | 0 | 2 | 7 |
7/7/2019 | 218 | 0 | 0 | 7 |
7/8/2019 | 2533 | 6 | 0 | 13 |
7/9/2019 | 2832 | 1 | 6 | 20 |
7/10/2019 | 2302 | 5 | 1 | 26 |
7/11/2019 | 2609 | 3 | 5 | 34 |
7/12/2019 | 2741 | 0 | 3 | 37 |
7/13/2019 | 287 | 0 | 0 | 37 |
Please help creating a calculation to derive Measure column
Thank you
Rohit
Hi, @Anonymous
I am a bit confused about what your desired outcome is. You want to have a table visual, right?
[Created date] and [Col 1] are fine, but I don't understand how Col 2 and Col 3 relates to [Col 1], except that you write that they are measures. And then you have a third measure you call measure, right?
In general, when you want to have a table visual with dates, and have a running total determined by a slicer, you need to have a separate date-table, without any relationship to the main table.
You can then write your query like this:
Running Total =
VAR _minDate =
CALCULATE ( MIN ( Dates[Date] ) )
VAR _currentDate =
CALCULATE ( SELECTEDVALUE ( Table[Created date] ) )
RETURN
CALCULATE (
SUM ( Table[Col1] );
FILTER (
ALL ( Table );
Table[Created date] <= _currentDate
&& Table[Created date] >= _minDate
)
)
The dates used in the table visual should not be from this special stand alone date-table, but from the main-table(or date dimension/table if your model has that). This will result in all rows showing, but rows outside the range specified by the slicer will have blank value for the running total. These rows can be filtered out by creating this measure:
AuxFilterMeasure =
VAR _maxDate =
CALCULATE ( MAX ( Dates[Date] ) )
VAR _minDate =
CALCULATE ( MIN ( Dates[Date] ) )
RETURN
IF (
SELECTEDVALUE ( Table[Created date] ) <= _maxDate
&& SELECTEDVALUE ( Table[created date] ) >= _minDate;
1;
0
)
and set it to filter=1 in the filter pane of the visual.
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |