Dynamically filter the contents of a matrix in specific conditions

[Anonymous]

Date	dateKey	Fiscal Year
29/9/2022	20220929	2022
30/9/2022	20220930	2022
01/10/2022	20221001	2023
02/10/2022	20221002	2023

I have a 'before' date slicer using the date above, linked to my fact table on the dateKey.

Sample Fact table:

CodeNumber	dateKey	FiscalYear	value	FlagFor350Code
299	20211001	2022	75	0
299	20220929	2022	100	0
299	20221001	2023	120	0
350	20210929	2021	140	1
350	20220929	2022	500	1
350	20221001	2023	550	1
400	20210929	2021	50	0
400	20220929	2022	60	0
400	20220930	2022	45	0
400	20221001	2023	65	0

In my matrix, I want to see everything up to that point/date (summed up), for each codeNumber, except for codenumber values of 350, in which case I only want the sum of the values which fall within the max fiscal year as selected in the date slicer.

eg. 

on 'to date' 01/10/2022 (ie fiscal year 2023):

codeNumber	total
299	75+100+120 = 295
350	550 only
400	50+60+45+65 = 220

on 'to date' 30/09/2022(ie fiscal year 2022): 

codeNumber	total
299	75+100 = 275
350	500 only
400	50+60+45 = 155

A calculated table doesn't work, since once loaded, it doesn't reload the calculations:

Table =

VAR _AsOfFiscalYear = CALCULATE(MAX('dwh DimDate'[FiscalYearNumerical]), ALL('dwh DimDate'))

RETURN

Filter('factTable',IF('factTable'[FlagFor350Code] = 1,'factTable'[FiscalYear] = _AsOfFiscalYear, TRUE()))

I've tried a number of measures, to no avail:

eg. 

Measure 4 =

VAR _AsOfFiscalYear = CALCULATE(MAX('dwh DimDate'[FiscalYearNumerical]), ALL('dwh DimDate'))

RETURN IF(AVERAGE('factTable'[FlagFor350Code]) = 0,1, if(AVERAGE('factTable'[FlagFor350Code]) = 1 && AVERAGE('factTable'[FiscalYear]) = _AsOfFiscalYear, 1,0))

Then I tried to filter the visual by measure 4 = 1.

I've simplified the issue a little, but it needs to be a matrix, and there are a range of codes which need to be excluded except for the max fiscalYear in addition to 350. 

 Can anyone help?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hey @Ashish_Mathur  - the link is not working

[Ashish_Mathur]

File attached here.

[DataInsights]

@Anonymous,

Try these measures:

Total Value = SUM ( FactTable[value] )

Total Measure = 
VAR vMaxFiscalYear =
    MAXX ( ALLSELECTED ( DimDate ), DimDate[Fiscal Year] )
VAR vTable =
    ADDCOLUMNS (
        VALUES ( FactTable[CodeNumber] ),
        "@Amount",
            VAR vCode350Amount =
                CALCULATE (
                    [Total Value],
                    FactTable[FlagFor350Code] = 1,
                    DimDate[Fiscal Year] = vMaxFiscalYear
                )
            RETURN
                IF ( FactTable[CodeNumber] = 350, vCode350Amount, [Total Value] )
    )
VAR vResult =
    SUMX ( vTable, [@Amount] )
RETURN
    vResult

---