Measure doesn't work correctly with slicer

[PwrBI01]

Hi guys,

I am trying to create a forecast for a period not ended. The measure is the next one:

Forecast = (Sales 12 last months / Sales 12 previous months * Total sales previous year) - Year-to-date sales

So, if the selected period is June 2020 the variables will be the following ones:

1. Sales 12 last months = Sales from July 2019 to June 2020

2. Sales 12 previous months = Sales from July 2018 to June 2019

3. Total sales previous year = Sales from January 2019 to December 2019

4. Year-to-date sales = Sales from January 2020 to June 2020

The problem is that when I introduce the measure on a chart and put a slicer for year 2020, the variables 'Sales 12 last months', 'Sales 12 previous months' and 'Total sales previous year' show a wrong result, because it doesn't take into account the sales from 2018 and 2019, so for example the result for 'Sales 12 previous months' is 0.

How can I solve this?

Thanks in advance.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[PwrBI01]

Hi @Anonymous and @Ashish_Mathur, 

Thank you for your answers.

I send you the link where the Power BI file is located so you can download it.

https://drive.google.com/file/d/1qRQ4cGYELIraQ1IzhVXeNsiCJZO46U-r/view?usp=sharing

I have created 2 columns, one for the results I obtain when I put the measure on a target and another column for the results I get when I filter the same measures for the year 2019 (I think this are the results the chart is showing) for trying to help you to understand.

Thanks in advance.

Regards.

[Anonymous]

HI @PwrBI01,

Can you please share some dummy data(similar to raw table structure) and expected results to help us clarify your requirement and test coding formula?

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng

[PwrBI01]

Hi @Anonymous,

I have share the link where you can find the file. 

https://drive.google.com/file/d/1qRQ4cGYELIraQ1IzhVXeNsiCJZO46U-r/view?usp=sharing

Thanks in advance.

Regards.

[Ashish_Mathur]

Hi,

In your column chart, drag Year from the Calendar Table.  See the image

[PwrBI01]

Thank you @Ashish_Mathur,

I would like to create the following chart:

I want to show the selected year and the previous one to do the measures you can see on the chart (I am trying to create them on Power BI) and if I use the solution you gave me I can just show one year, ¿is there another way to do it in which you can show the selected year and the previous one?

Thanks in advance and sorry for the inconvenience.

Regards.

[Anonymous]

HI @PwrBI01,

You can create a calculated table with two labels that you mentioned, then you can use raw table date fields on the chart and new table fields as legend, write measure formulas with switch function to show different result based on current date and legend.

DAX – Making the “Case” for SWITCH() 

After these steps, you need to create a slicer with date fields that not linked to the current table as source and write a measure to compare the current date and selected date. Then you can use this measure on your chart 'visual level filter' to filter records of selected year and previous year.

Applying a measure filter in Power BI 
Regards,

Xiaoxin Sheng

[PwrBI01]

Hi @Anonymous, 

Thank you for your answer,

That data are the ones I should obtain if the period selected is June 2019 as in the example, but they depends on the period selected in the slicer, they are not always the same data.

Is there a way to create a calculate table with that data changing depending on the slicer? I am not able to do that step

Thanks in advance.

Regards.

[Anonymous]

HI @PwrBI01,

Unfortunately, current power bi does not support to create dynamic calculated column/table that based on filter or slicer selections. They are host on different levels and you can't use child-level filters to interact with its parent.

Notice: the data level of power bi.
Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[amitchandak]

@PwrBI01 , Use month in the slicer from the date table and try measures like

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31")) //complete
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) //complete

Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH)) //last 12

// 12 before 12

Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))
Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))
Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd('Date'[Date],-12,month)),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[PwrBI01]

Hi @amitchandak, thank you for your answer.

I have tried that way but it doesn't work.