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 everyone,
I have a Fact-tabel (called 'Fact') where I need to count the rows as a running sum (for 5 years) and a date tabel ('Date') to use the time sensing-DAX.
I want to calculate the running sum for the number of rows in the Fact-tabel, sorted for a single column/dimension in this tabel (called 'Fact'[Fac_cluster]).
The measure RunningSum is calculated as follows:
RunningSum =
VAR NumOfYears = 5
VAR LastCurrentDate =
MAX ( 'Date'[Date] )
VAR Period =
DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfYears, YEAR )
VAR Result =
CALCULATE (
SUMX(
VALUES ( 'Date'[Year] ),
COUNTROWS ( 'Fact' )
),
Period
)
VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )
VAR LastDateWithSales = MAX ( 'Fact'[Start date] )
RETURN
IF ( FirstDateInPeriod <= LastDateWithSales, Result )
This works as long as the dimension 'Fact'[Fac_cluster] exists for a given year, but gives a blank result when the dimension is non-existing.
For example: RC doesn't have any rows in the year 2019, but I stil want to count the running sum. Should be 4 for RC in 2019 in the table below but the measure RunningSum returns a blank in the matrix visual.
Does anyone know what adaptation I have to make to the measure?
Thanks a lot
Solved! Go to Solution.
@kegoosse , Can you try a simpler version, also make a sure the year is from the date table
Cumm = CALCULATE(countries(Fact) ,filter(allselected(date),date[date] <=max(date[Date])))
@amitchandak Thank you, simplifying the formula was the right idea.
Finally ended up with:
RunningSum = CALCULATE(
COUNTROWS('Fact'),
filter(
ALLEXCEPT('Date','Date'[Year]),
'Date'[Date] <= max('Date'[Date]) && 'Date'[Date] >= DATE(YEAR(max('Date'[Date]))-5,MONTH(max('Date'[Date])),DAY(max('Date'[Date])))
)
)
@kegoosse , Can you try a simpler version, also make a sure the year is from the date table
Cumm = CALCULATE(countries(Fact) ,filter(allselected(date),date[date] <=max(date[Date])))
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 |
---|---|
48 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |