Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys,
I've a table like this
This table is linked to calendar table.
In report I use a filter to set a time period, for example: from 19/03/2024 to 30/04/2024.
I need to create a matrix:
Type amount
A ..........
B ...........
My measure must calculate the sum of type A from beginning of the year to until the day before the period I selected (18/03/2024) and the sum of type B from 19/03/2024 to 30/04/2024.
filter date: from 19/03/2024 to 30/04/2024
For example:
Type amount
A 600+120+23+77=820
B 1+72=72
Thank you all 🙂
Solved! Go to Solution.
Thanks for the reply from @tamerj1 , please allow me to provide another insight:
Hi @giuliapiazza94 ,
Here are the steps you can follow:
1. Create calculated table – slicer table.
Date =
CALENDAR(
DATE(2024,1,1),DATE(2024,4,30))
2. Create measure.
Measure =
var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date])
var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date])
var _perioddate=_mindate-1
var _until=
DATE(YEAR(_mindate),1,1)
return
IF(
MAX('Table'[Type])="A",
SUMX(
FILTER('Table',
'Table'[Date]>=_until&&'Table'[Date]<=_perioddate),[Amount]),
SUMX(
FILTER('Table',
'Table'[Date]>=_mindate&&'Table'[Date]<=_maxdate),[Amount]))
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks for the reply from @tamerj1 , please allow me to provide another insight:
Hi @giuliapiazza94 ,
Here are the steps you can follow:
1. Create calculated table – slicer table.
Date =
CALENDAR(
DATE(2024,1,1),DATE(2024,4,30))
2. Create measure.
Measure =
var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date])
var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date])
var _perioddate=_mindate-1
var _until=
DATE(YEAR(_mindate),1,1)
return
IF(
MAX('Table'[Type])="A",
SUMX(
FILTER('Table',
'Table'[Date]>=_until&&'Table'[Date]<=_perioddate),[Amount]),
SUMX(
FILTER('Table',
'Table'[Date]>=_mindate&&'Table'[Date]<=_maxdate),[Amount]))
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @giuliapiazza94
Please try
AmountMeasure =
SUMX (
VALUES ( 'Table'[Type] ),
IF (
'Table'[Type] = "A",
CALCULATE (
SUM ( 'Table'[Amount] ),
'Calendar'[Date] < MIN ( 'Calendar'[Date] )
),
SUM ( 'Calendar'[Amount] )
)
)
User | Count |
---|---|
58 | |
21 | |
18 | |
16 | |
13 |
User | Count |
---|---|
85 | |
54 | |
45 | |
39 | |
21 |