Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ) )
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |