Measure doesn't filter things accordingly using a parameter

[daniel_baciu]

Hi,

Could you please help me fix the "Value 6" measure formula to only show data as in "Value 4" column? Data should be visible only up to P04 (which is the parameter i want to set up automatically).

I tried several ways of getting the parameter input automatically, but it doesn't take it at all, except if i input the parameter manually. 

Both formulas are the same, just that "Value 4" measure has a manual input parameter (number "4"), while the parameter used in the "Value 6" column formula is built using a measure (created with SELECTEDVALUE formula).

"Value 4" column DAX formula is:

Value 4 = 
var CP = 4
return
if(
    max('data orders & sales demo'[numeric period]) <= CP && max('Dim Calendar'[Fiscal Year]) = 2023,
        CALCULATE (
            [Value k$],
            DATESINPERIOD('Dim Calendar'[Date], LASTDATE(DATEADD('Dim Calendar'[Date], 0, MONTH)), -12, MONTH),
                ( 'Data Orders & Sales Demo'[Scenario] = "ActualsFixed"   && 'Data Orders & Sales Demo'[Year] = "2023")    ||
                ( 'Data Orders & Sales Demo'[Scenario] = "PORFixed"      && 'Data Orders & Sales Demo'[Year] <> "2023")
        ),
    BLANK()
    )

 "Value 6" column DAX formula is:

Value 6 = 
var CP = [Current Period]
return
if(
    max('data orders & sales demo'[numeric period]) <= CP && max('Dim Calendar'[Fiscal Year]) = 2023,
        CALCULATE (
            [Value k$],
            DATESINPERIOD('Dim Calendar'[Date], LASTDATE(DATEADD('Dim Calendar'[Date], 0, MONTH)), -12, MONTH),
                ( 'Data Orders & Sales Demo'[Scenario] = "ActualsFixed"   && 'Data Orders & Sales Demo'[Year] = "2023")    ||
                ( 'Data Orders & Sales Demo'[Scenario] = "PORFixed"      && 'Data Orders & Sales Demo'[Year] <> "2023")
        ),
    BLANK()
    )

where [Current Period] is:

Current Period = SELECTEDVALUE('Data Orders & Sales Demo'[Numeric Period])

I have a Calendar table ('Dim Calendar") and Orders and Sales data table ("Data Orders & Sales Demo") which are joined using a bridge:

Here is the pbix: https://drive.google.com/file/d/1exD3cvlZ5RiafUXoWaFzECPkHn1DsH4H/view?usp=sharing

Other workaround is welcomed as well - the aim is to get the data up to a period selected in a slicer in another page of the report, using below calculation:

 CALCULATE (
            [Value k$],
            DATESINPERIOD('Dim Calendar'[Date], LASTDATE(DATEADD('Dim Calendar'[Date], 0, MONTH)), -12, MONTH),
                ( 'Data Orders & Sales Demo'[Scenario] = "ActualsFixed"   && 'Data Orders & Sales Demo'[Year] = "2023")    ||
                ( 'Data Orders & Sales Demo'[Scenario] = "PORFixed"      && 'Data Orders & Sales Demo'[Year] <> "2023")
        ),

Thanks in advance!

[olgad]

Hi, if you drop the current period measure on your table you will see no 4 but the respective period, 1,2,3 because for every row the selected period is the one of the context of the row. You need to create a parameter not sitting in your calendar table, so the context doesnt interfere anymore.

Adjust your Value 6 

SELECTEDVALUE(Period[Period]) (My parameter is called Period)

[daniel_baciu]

Thank you for your reply.

I assume this is implying manually selecting the parameter every time i need to see another period.

What if I want this to change automatically whenever the period is changed in the index page? 

[olgad]

Daniel, so, one question, when zou select on index page p4 does it filter other visuals for p4 only or like with this measure upto p4? the slicer you can put on all the pages, hide it in the selection pane and  you can syncronize for those slicers for all the pages.

[daniel_baciu]

Hi Olga,

yes, that slicer is filtering other visuals in the project; but I don't want that slicer to filter the page where i will use those measures (ie Run Rate). That's because i'll use it in a chart where i need to see all periods.

The ultimate goal is to automatically update the line chart (to be found in tab Run Rate) every time the period is changed in tab index. Meaning the dark blue is switching to a previous or a future period, as selected in the Period slicer in tab index...

"Value 6" column is an extract of the formula in that line chart (you can check "Value RR 12 Mths k$ Actuals 3" measure which is included in the line chart).

Correct, that's how i proceeded with the slicer to have it syncronize all the other visuals, but not this one, as i need to see all the periods (for the POR line) but only the past periods (for Actuals)...