Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have requirement to show sale amount from current filter date and year until the last month of the year.
Start Date | Amount |
01-Jan-2023 | 1000 |
01-Feb-2023 | 500 |
01-Apr-2023 | 2000 |
From the table, the requirement is If I select period Jan-23 report will show
Jan-23 | Feb-23 | Mar-23 | Apr-23 | May-23 | Jun-23 | Jul-23 | Total |
1000 | 500 | 0 | 2000 | 0 | 0 | 0 | 3500 |
If I select period Feb-23 report will show
Jan-23 | Feb-23 | Mar-23 | Apr-23 | May-23 | Jun-23 | Jul-23 | Total |
0 | 500 | 0 | 2000 | 0 | 0 | 0 | 2500 |
Can anyone help use to create measure for this requirement?
Regards,
JP
Solved! Go to Solution.
Hi @JP08 ,
Please try below steps:
1. below is my test table
Table:
Table2:
2. cretae a measure with below dax formula
Measure =
VAR _sd =
SELECTEDVALUE ( 'Table'[Start Date] )
VAR _a =
SELECTEDVALUE ( 'Table 2'[Date] )
VAR _val =
CALCULATE ( MAX ( 'Table'[Amount] ), 'Table'[Start Date] = _a )
VAR _val1 =
IF ( ISBLANK ( _val ), 0, _val )
VAR _b =
FILTER ( ALL ( 'Table' ), [Start Date] >= _sd )
RETURN
IF (
ISFILTERED ( 'Table 2'[Date] ),
IF ( _a < _sd, 0, _val1 ),
SUMX ( _b, [Amount] )
)
3. add a slicer with Table field, add a matrix with Table2 field and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @JP08 ,
Please try below steps:
1. below is my test table
Table:
Table2:
2. cretae a measure with below dax formula
Measure =
VAR _sd =
SELECTEDVALUE ( 'Table'[Start Date] )
VAR _a =
SELECTEDVALUE ( 'Table 2'[Date] )
VAR _val =
CALCULATE ( MAX ( 'Table'[Amount] ), 'Table'[Start Date] = _a )
VAR _val1 =
IF ( ISBLANK ( _val ), 0, _val )
VAR _b =
FILTER ( ALL ( 'Table' ), [Start Date] >= _sd )
RETURN
IF (
ISFILTERED ( 'Table 2'[Date] ),
IF ( _a < _sd, 0, _val1 ),
SUMX ( _b, [Amount] )
)
3. add a slicer with Table field, add a matrix with Table2 field and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |