Line Chart with Slider selection

[sudhakar111]

Hello All,

Below is my issue, any help is appreciated.

There are 2 tables one is the Calendar table and other is Invoice table.

Both are linked using the Date field in Calendar table and Invoice billed date in Invoice table.

The report has a year filter using a slider to select the years which is based on year field in Calendar table.

To capture the Max and Min years i am using the below measures.

MAX_YEAR = CALCULATE(MAX('Calendar'[Year]),ALLSELECTED('Calendar'[Year]))

MIN_YEAR = CALCULATE(MIN('Calendar'[Year]),ALLSELECTED('Calendar'[Year]))

We want to see the Invoice Billed Quantity along with the month name in a line chart.

One line should show the billed quantity based on the maximum selected year on the year slider and other line should show the billed quantity based on minimum year on the year slider.

Below are the formula for the Max and Min lines.

MAX_QTY = calculate(sum(INVOICE[BILLED_QTY]),filter(INVOICE,max('INVOICE'[Year])=[MAX_YEAR]))

MIN_QTY = calculate(sum(INVOICE[BILLED_QTY]),filter(INVOICE,min('INVOICE'[Year])=[MIN_YEAR]))

I am using the above 2 measures and month name in the line chart.

So based on the slider selection if the year is 2023, the MAX_QTY field is bringing information for 2023 and 2022 as well.

Each line should display quantities for the selected years only.

[lbendlin · Accepted solution]

see attached.

[sudhakar111]

Thank you both @Ashish_Mathur @lbendlin . Both the solutions are working perfectly. 

[Ashish_Mathur]

Hi,

Try these measures

MIN_QTY = calculate(sum(INVOICE[BILLED_QTY]),DATESBETWEEN('Calendar[Date],min('Calendar'[Date]),min('Calendar'[Date])))

MAX_QTY = calculate(sum(INVOICE[BILLED_QTY]),DATESBETWEEN('Calendar[Date],max('Calendar'[Date]),max('Calendar'[Date])))

To your visual, drag Year from the Calendar Table to the legend and Month name from the Calendar Table to the X-axis.

Hope this helps. 

[sudhakar111]

Thanks Ashish for the response, I tried the measures given by you. When the year 2023 is selected on the slider it is showing information for the future months also.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[sudhakar111]

@Ashish_Mathur @lbendlin  Please find the sample PBIX file. 

https://drive.google.com/file/d/1m2sMQByb5M2bLE3UBOkVpalIikq5aHAZ/view?usp=share_link

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[lbendlin · Accepted solution]

see attached.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523