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.
I have a costs statement source in the below format, I would like to return the Plan 2019 cost figure only to last month... so this month is currently May, I only want the sum from Jan to Apr returned
Table 1 / Costs ----------------------------- TimePeriod | Date mm/dd/yyyy | Value ----------------------------- A18 | 01/01/2018 | 12345 A18 | 02/01/2018 | 12345
... A18 | 12/01/2018 | 12345 A19 | 01/01/2019 | 12345
A19 | 02/01/2019 | 12345
A19 | 03/01/2019 | 12345
A19 | 04/01/2019 | 12345
P19 | 01/01/2019 | 12345
P19 | 02/01/2019 | 12345
...
P19 | 12/01/2019 | 12345
I have a date table(Dates) which I have a relationship to the Date column above
Solved! Go to Solution.
Hi @SingSong ,
To create a measure as below.
Measure = VAR pre = CALCULATE ( MAX ( 'CALENDAR'[Date] ), FILTER ( 'CALENDAR', DATEDIFF ( 'CALENDAR'[Date], TODAY (), MONTH ) = 1 ) ) RETURN CALCULATE ( SUM ( Table1[value] ), FILTER ( Table1, Table1[TimePeriod] = "P19" && YEAR ( 'Table1'[date] ) = YEAR ( pre ) && 'Table1'[date] <= pre ) )
Hi! I came across this looking for a solution for a similar problem. I wanted to offer up the solution I came up with as an alternative or for anyone who might be looking in the future...
CALCULATE(
TOTALYTD (
[Forecasted Contract Count],
'Calendar'[Date]
),
INTERSECT(
DATESYTD('Calendar'[Date]),
PREVIOUSMONTH('Calendar'[Date])
)
)
Hi @SingSong ,
To create a measure as below.
Measure = VAR pre = CALCULATE ( MAX ( 'CALENDAR'[Date] ), FILTER ( 'CALENDAR', DATEDIFF ( 'CALENDAR'[Date], TODAY (), MONTH ) = 1 ) ) RETURN CALCULATE ( SUM ( Table1[value] ), FILTER ( Table1, Table1[TimePeriod] = "P19" && YEAR ( 'Table1'[date] ) = YEAR ( pre ) && 'Table1'[date] <= pre ) )
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |