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,
ref sample file.
i have managed to get the dax working for the previous daily, monthly & quarterly cummulative/running totals. however, i'm having problems to get the same for previously week and yearly cummulative/running totals.
1st i get the sum of newcases with
SUM( 'cv19-data'[New_cases] )
then, for previous day, month, quarter, year calculation, i use the DATEADD function such as
CALCULATE ( [NewCases], DATEADD ( 'Calendar'[Date], -1, DAY ) )
or
CALCULATE ( [NewCases], DATEADD ( 'Calendar'[Date], -1, MONTH ) )
or
CALCULATE ( [NewCases], DATEADD ( 'Calendar'[Date], -1, QUARTER ) )
or
CALCULATE ( [NewCases], DATEADD ( 'Calendar'[Date], -1, YEAR ) )
for previous week, i use
VAR CurrentWeek = SELECTEDVALUE( 'Calendar'[WeekNo] )
VAR CurrentYear = SELECTEDVALUE( 'Calendar'[Year] )
VAR MaxWeekNo = CALCULATE( MAX ( 'Calendar'[WeekNo] ), ALL ( 'Calendar' ) )
RETURN
SUMX( FILTER( ALL ( 'Calendar' ),
IF ( CurrentWeek = 1, 'Calendar'[WeekNo] = MaxWeekNo
&& 'Calendar'[Year] = CurrentYear -1,
'Calendar'[WeekNo] = CurrentWeek -1
&& 'Calendar'[Year] = CurrentYear )
),
[NewCases] )
for the cummulative/running totals calculations, i have no issues with the previous day, previous month, & previous quarter. i need help to find the cummulative/running totals for previous week and previous year.
thanks in advance for any help.
krgds, -nik
Solved! Go to Solution.
@Anonymous , Ideally this formula should work as cumulative for day week and month etc
Cumm Sales = CALCULATE([new case], filter(allselected(Calendar),Calendar[date] <=max(Calendar[Date])))
Use week rank as a new column and use that for week related stuff
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measures
This Week = CALCULATE([new cases], FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE([new cases], FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Cumm Week = CALCULATE([new cases], FILTER(ALL('Date'),'Date'[Week Rank] <=max('Date'[Week Rank])))
@Anonymous , Ideally this formula should work as cumulative for day week and month etc
Cumm Sales = CALCULATE([new case], filter(allselected(Calendar),Calendar[date] <=max(Calendar[Date])))
Use week rank as a new column and use that for week related stuff
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measures
This Week = CALCULATE([new cases], FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE([new cases], FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Cumm Week = CALCULATE([new cases], FILTER(ALL('Date'),'Date'[Week Rank] <=max('Date'[Week Rank])))
tks again, @amitchandak.
i hv also managed to find week-over-week calc.
krgds, -nik
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 |