Convert Calculations based on date inteligence into table

[George1973]

Hi DAX Gurus 🙂

There is a very simple measure calculating "Product Sold Quantity":

Sold Prod Qnty = 
CALCULATE
(SUM(TS_OPERATIONS_DOP[KOL]),
FILTER(TS_OPERATIONS,TS_OPERATIONS[OPER_TYPE_ID] == 1 && TS_OPERATIONS[TYP] = 47 
|| 
TS_OPERATIONS[OPER_TYPE_ID] == 38 && TS_OPERATIONS[TYP] = 47),
FILTER(TS_OPERATIONS,  TS_OPERATIONS[TYP] = 47 && TS_OPERATIONS[IP$FLAGS] <> 4099))

Nothing special. This measure is linked to the table with relations with the Date dable and all the calculatings with the date related total quantities are fine.

Then I have some other formulas, calculating the same results, but -1, -2, -3... etc months ago, like given below:

-1M = CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-1,MONTH))
-2M = CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-2,MONTH))
.....

OK. Thouse formulas are fine too.

And now comes the hardest point:
For each previous months, I have seperate, let's say "smoothing data" scenario: Meaning that, for the current months, where formula is [Sold Product Qnty], the scenario is #1, for the [-1M] is Scenario #2.. etc..
I have successed to write a measure, namely a code to get all scenarios, for the previous 12 months seperately. But, the probelem is, that, these scenarios I have  to keep seperately, like [-1M Scenario], [-2M Scenario]... etc..

That's got me some the following challanges:
- I can not bind those peratete scenarios onto Data Table, as easy as I have with [Sold Prod Quantity"] measure
- The calculation time is beeing increased dramaticaly

So, he question is how can I come out from this? - I presume, I have to create a table, unioning the scenarios and real quntitative sales into one "umbrella" and linking them to Data Table.. 

How should I do it? Can you give me some hints?

Thanks in advance,

[George1973 · Accepted solution]

Hi,
I have soled the problem with the following approach:

VAR CurrDate=SELECTEDVALUE(DateKey[MonthIndex])
VAR MaxDate=MAX(DateKey[MonthIndex])

VAR Fut_Dem=
SWITCH(true(),
        MaxDate=CurrDate-1,Data_1,   
        MaxDate=CurrDate-2,Data_2,
        MaxDate=CurrDate-3,Data_3,
        MaxDate=CurrDate-4,Data_4,
        MaxDate=CurrDate-5,Data_5,
        MaxDate=CurrDate-6,Data_6,
        MaxDate=CurrDate-7,Data_7,
        MaxDate=CurrDate-8,Data_8,
        MaxDate=CurrDate-9,Data_9,
        MaxDate=CurrDate-10,Data_10,
        MaxDate=CurrDate-11,Data_11,
Data_0)

       

Return
Fut_Dem

[George1973]

I'm trying to solve it with this solution, but I'm getting the error:

VAR CurrDate=SELECTEDVALUE(DateKey[Date])
VAR MaxDate=MAX(DateKey[Date])

VAR Fut_Dem=
SWITCH(true(),
        MaxDate=calculate(CurrDate,DATEADD(DateKey[Date],-1,MONTH),Data_1),   
        MaxDate=calculate(CurrDate,DATEADD(DateKey[Date],-2,MONTH),Data_2),
        MaxDate=calculate(CurrDate,DATEADD(DateKey[Date],-3,MONTH),Data_3),
        MaxDate=calculate(CurrDate,DATEADD(DateKey[Date],-4,MONTH),Data_4),
        MaxDate=calculate(CurrDate,DATEADD(DateKey[Date],-5,MONTH),Data_5),
        MaxDate=calculate(CurrDate,DATEADD(DateKey[Date],-6,MONTH),Data_6),
        MaxDate=calculate(CurrDate,DATEADD(DateKey[Date],-7,MONTH),Data_7),
        MaxDate=calculate(CurrDate,DATEADD(DateKey[Date],-8,MONTH),Data_8),
        MaxDate=calculate(CurrDate,DATEADD(DateKey[Date],-9,MONTH),Data_9),
        MaxDate=calculate(CurrDate,DATEADD(DateKey[Date],-10,MONTH),Data_10),
        MaxDate=calculate(CurrDate,DATEADD(DateKey[Date],-11,MONTH),Data_11),
Data_0)

       


Return
Fut_Dem

[George1973]

One clarification: Whay I need it, ok.. let's see the following chart:

The Blue Line is the "Smoothed" data, of the previous months.. I used excel to have my seperated "Scenarios" on one table together with the original data.. and now I got it. But, I need it in Power BI - Automated. 🙂