DAX calculation that calls to a parameter

[Anonymous]

I have a line chart that shows sales data by fiscal week and I want to be able to display only up through the selected fiscal week. There is a parameter [fiscal week selection] that allows viewers to input what fiscal week they want to see metrics on and I want this to be the end of the range of data in my line chart. So I need a calculation that filters the line chart to show a line from fiscal week 1 through to [fiscal week selection]. I can do this range using a number with this calculation:

inserted =

CALCULATE (

[Sum of Opps (All opps)],

FILTER (

( 'CALENDAR' ),

'CALENDAR'[Fiscal Week]

> 0 && 'CALENDAR'[Fiscal Week] <= 20

), 'CALENDAR'[Fiscal Year]=2019

)

but when I try to put [fiscal week selection] in place of 20, it does not display the data correctly. I do not want to add a slicer to filter my chart as this adds yet another element viewers have to change/input. I want them to be able to choose a fiscal week and see the metrics for that week as well as have the chart change to display only up through the selected week. 

[Anonymous]

@Anonymous - You need to use a variable, like this:

inserted =
var selected_fiscal_week = MAX('CALENDAR'[Fiscal Week])
return CALCULATE (
[Sum of Opps (All opps)],
FILTER (
( 'CALENDAR' ),
'CALENDAR'[Fiscal Week]
> 0 && 'CALENDAR'[Fiscal Week] <= selected_fiscal_week
), 'CALENDAR'[Fiscal Year]=2019
)

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Anonymous]

@Anonymous this does not filter my chart, it still shows up through the current fiscal week (35) and does not change the line when the parameter value is changed. 

[Anonymous]

@Anonymous  - What do you mean by a parameter value? Could you share a screenshot?

Thanks,

Nathan

[Anonymous]

@Anonymousso there's a "What if" parameter on the page with a series generated from 1-53,  

[Anonymous]

@Anonymous - The measure will need to refer to the "what if parameter":

inserted =
var selected_fiscal_week = MAX('Your What If Parameter Table'[Your What If Parameter Column])
return CALCULATE (
    [Sum of Opps (All opps)],
   FILTER (
        'CALENDAR',
        'CALENDAR'[Fiscal Week] > 0 && 'CALENDAR'[Fiscal Week] <= selected_fiscal_week
    ), 
    'CALENDAR'[Fiscal Year]=2019
)

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Anonymous]

@Anonymous I have done that and it continues to show to the current week, even when I change the parameter

inserted2 =

var selected_fiscal_week = MAX(Parameter[Fiscal Week])

return CALCULATE (

[Sum of Opps (All opps)],

FILTER (

'CALENDAR',

'CALENDAR'[Fiscal Week] > 0 && 'CALENDAR'[Fiscal Week] <= selected_fiscal_week

),

'CALENDAR'[Fiscal Year]=2019

)

[Anonymous]

@Anonymous  - Could you share your pbix and expected result? Or screenshot of all pertinent info and expected result?

Thanks,

Nathan

[Anonymous]

@Anonymous 

My line chart is created using these measures:

2018 Web Opps = CALCULATE([Sum of Opps (All opps)], FILTER('Calendar','Calendar'[Fiscal Year]=2018),OPPS[VEHICLE_RESPONSE_CODE]="Web")

2019 Web Opps = CALCULATE([Sum of Opps (All opps)], FILTER('Calendar','Calendar'[Fiscal Year]=2019),OPPS[VEHICLE_RESPONSE_CODE]="Web")

Sum of Web Opps Goals = SUM('2019 Goals Data (Web Opps)'[Web Opps Goal])

- All of my tables are connected to a business calendar table by business date, allowing me to also connect them by fiscal week

- what I need is for the 2019 Web Opps line to filter from fiscal week 1 to the selected fiscal week

- I have a slicer on the page that is connected to a what if parameter that goes from 1-53 and allows for the viewer to select a fiscal week to view

- the "inserted" measure I have above is a test to see if I can filter the Web Opps, which I have not been able to.

I set a variable as Parameter[Fiscal Week] which when I click on it shows this:

Sum of Web Opps Goals = SUM('2019 Goals Data (Web Opps)'[Web Opps Goal])

And I have also tried setting the variable in inserted to Parameter[fiscal Week Selection] which when I click shows the measure is calculated like this:

Fiscal Week Selection = SELECTEDVALUE('Parameter'[Fiscal Week])

My Parameter is also connected to my calendar table by fiscal week (1-53)

[Anonymous]

@Anonymous  - I'm not sure what else to try, except some troubleshooting steps. Your original measure works with a hard-coded max week. The measure with a variable based on MAX(Parameter[Fiscal Week]) is not working, so then I'd test the value for MAX(Parameter[Fiscal Week]) in a separate measure. If that is giving the proper value, then the only way for the measure with variable to be wrong is for relationships to be messing it up somehow. But, to troubleshoot, I'd need the pbix.

Thanks,

Nathan