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 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] )
)
)
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
53 | |
46 | |
15 | |
12 |