Problems calculating if there was a product-placement in the last 12 months before the filtered date

[martinobermayr]

Hi folks.

I would like to calculate if a productplacement is new.

Tables

• Table 'Sales' with every invoice, customer, product etc. per date
• Table 'Calendar' with date-connection to 'Sales' and further fields as MM/YY and so on

Measures

• Measure 'Placement' which does a DISTINCTCOUNT on a combined-textfield "customernumber+yymm+product" to see if a product was placed at the customer in this YYMM-periode:
  
  PLACEMENT = DISTINCTCOUNT('Sales'[CUSTOMER+YYMM+PRODUCT])
  
  Showing this in a table seems to be correct:
  • 1st table = two products with 5 placements
  • 2nd table = these product placements each mm/yy
  • 3rd table = each invoice-line per date
    
    
• So far so good (and correct) but now i would really love to see, wheter a product was already placed before or placed the first time:
  • As you can see in the 3rd table, the product "DECARBONIZER", sold on 7.11.22, was never sold before -> So i would like to tag it as a new placement with '1'
  • The "A+M-FINISH" was sold a few times before -> So it should be tagged as a new placement with '1' in the earliest invoiceline from 06.11.21. Every later invoice-line should not be tagged because of already existing
  • Based on the data mentioned before, i would like to get a table, that should deliver this result:
    (this table was 'photoshoped' to simulate what i would like to get)
    but i really dont know how to solve this!?

Could someone please help me?
Thank you in advance!

[Anonymous]

Hi @martinobermayr ,

I'm so sorry I missed it. Due to privacy concerns, we do not have the right to view and reply to private messages. I cannot reproduce your issue.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[martinobermayr]

Hi @Anonymous .

Sorry, did not knew the privacy concern due to PMs.
Please read my current status in my latest post: Re: Problems calculating if there was a product-pl... - Microsoft Power BI Community
The pbix-Downloadlink: https://we.tl/t-7wL5ZYMMqz
(unfortunately i cannot upload it into this thread, dont know why)

[Anonymous · Accepted solution]

Hi @martinobermayr  ,

You can refer to the blog to optimize the dax.

Best practice rules to improve your model’s performance | Microsoft Power BI Blog | Microsoft ...

1. Download and install Tabular Editor .

2. Open Tabular Editor and run the following code in the Advanced scripting window.

 

System.Net.WebClient w = new System.Net.WebClient(); 
string path = System.Environment.GetFolderPath(System.Environment.SpecialFolder.LocalApplicationData);string url = "https://raw.githubusercontent.com/microsoft/Analysis-Services/master/BestPracticeRules/BPARules.json";string downloadLoc = path+@"\TabularEditor\BPARules.json";
w.DownloadFile(url, downloadLoc);

 

3. Close and reopen Tabular Editor.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @martinobermayr ,

Please have a try.

Create a measure.

measure =
VAR _1yearbefore =
    EDATE ( TODAY (), -12 )
RETURN
    CALCULATE (
        MIN ( TABLE[DATE] ),
        FILTER (
            ALL ( TABLE ),
            TABLE[DATE] >= _1yearbefore
                && table[familie] = SELECTEDVALUE ( table[familie] )
        )
    )

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[martinobermayr]

Hi @Anonymous.

Thanks for your reply - i think that took me one step closer to my final solution.

I integrated the code you gave me, and now the measure shows the date of the first product-placement, as we can see in the screenshot in the last column:

Thats correct so far.

But in my final solution i now would like, that only the 07.12.21-Line is tagged as "1" (for new)
To simulate the desired output it should look like this:

May i ask you how your DAX should be modified to reach this goal?

Thank you very much.

[Anonymous]

Hi @martinobermayr ,

Please modify the measure.

measure =
VAR _1yearbefore =
    EDATE ( TODAY (), -12 )
VAR _2result =
    CALCULATE (
        MIN ( TABLE[DATE] ),
        FILTER (
            ALL ( TABLE ),
            TABLE[DATE] >= _1yearbefore
                && table[familie] = SELECTEDVALUE ( table[familie] )
        )
    )
RETURN
    IF ( MAX ( TABLE[DATE] ) = _2result, _2result, BLANK () )

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[martinobermayr]

Good morning @Anonymous .

Perfect, it's getting better and better. I thought that this is the solution now, but there is one (little?) question left:

Adding this:

IF ( MAX ( TABLE[DATE] ) = _2result, _2result, BLANK () )

Shows the date out from _2result:

But i want a countable "1" instead, to measure and sum how good the sales-performance on placeing new products is. So when i replace your "_2result" with a simple "1", i thought i could solve my needs:

IF( MAX ( TABLE[DATE] ) = _2result, 1, BLANK () )

Result seems to be right:

Yellow = correct 👍

Red = Here should be the column sum

Is there any way to provide this?

Measure is formated as number:

Thank you so much!

[Anonymous]

Hi @martinobermayr ,

It seems you want to show the measure correctly total.

Please have a try.

Create a measure based on [measure].

result=var _b=summarize(table,table[date],"aaa",[measure])
return
if(hasonevalue(table[date]),[measure],sumx(_b,aaa))

 Also you can use the unique value in the column replace the date column.

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[martinobermayr]

Hi @Anonymous.

Yep, that was the solution to sum it. - Thank you very very much:

Unfortunately after testing this function with a few examples, i found out that there is a fundamental part missing:

Currently this new placement measure calculates, when the product was first time placed in the last 12 months, outgoing from today's date:

VAR _1yearbefore =
    EDATE ( TODAY(), -12 )

But what i actually have to analyze is, if the product was sold 12 months before the respective line.

Can cou please give me an idea of what i should insert instead of TODAY() in this EDATE, to get the respective line date?

Sorry, I hope i could explain it understandable!?

[Anonymous]

Hi @martinobermayr ,

Please have a try.

measure= var _maxdate =
MaxX (
    FILTER ( ALL ( table ), table[familie] = SELECTEDVALUE ( table[familie] ) ),
    table[date]
)
var _1year=
    EDATE ( _maxdate, -12 )
VAR _2result =
    CALCULATE (
        MIN ( TABLE[DATE] ),
        FILTER (
            ALL ( TABLE ),
            TABLE[DATE] >= _1yearbefore
                && table[familie] = SELECTEDVALUE ( table[familie] )
        )
    )
RETURN
    IF ( MAX ( TABLE[DATE] ) = _2result, _2result, BLANK () )

If it still does not help, please provide pbix file without privacy information so that I can help you get the soultion quickly!!!

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[martinobermayr]

Hi @Anonymous 

I updated the DAX but it is loading and loading and so a tried and checked every single step, step by step:

1. Step: Returning the _linedate is correct:

2. Step: Returning the _1yearbefore is also correct:

3. Step: Returning _2result

Report is loading and loading without showing any result after > 10 minutes
(although this VAR has not been modified after we had the _1yearbefore on TODAY() before)

[..]
VAR _2result =
    CALCULATE (
        MIN ( 'Sales'[Date] ),
        FILTER (
            ALL ( 'Sales' ),
            'Sales'[Date] >= _1yearbefore
                && 'Sales'[family] = SELECTEDVALUE ( 'Sales'[family] )
        )
    )

RETURN
_2result
[..]

Can you imagine why?

Ps: You wrote "If it still does not help, please provide pbix file without privacy information so that I can help you get the soultion quickly!!!" and i am sorry for interrupting you so much. But in every step i thought "now we are very close to the solution" and otherwise i do not really know how to give you the pbix file withouth privacy informations in a fast way. (pbix has 360 MB with millions of entries in over 40 tables and i do not know how i can fastly minimize the entries and tables to send it to you... Should we make a teamscall instead?

Sorry for that.

[Anonymous]

Hi @martinobermayr ,

What is the _2result? It still errors?

measure = 
VAR _maxdate =
    MAXX (
        FILTER ( ALL ( sales ), sales[familie] = SELECTEDVALUE ( sales[familie] ) ),
        sales[date]
    )
VAR _1year =
    EDATE ( _maxdate, -12 )
VAR _2result =
    MINX (
        FILTER (
            ALL ( sales ),
            sales[date] >= _1year
                && sales[familie] = SELECTEDVALUE ( sales[familie] )
        ),
        sales[date]
    )
RETURN
    IF ( MAX ( sales[date] ) = _2result, _2result, BLANK () )

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[martinobermayr]

Hi @Anonymous .

May i kindly ask you again if you could please help me or give me an idea of where i could get helped? Please find my further informations in my latest post and in the PM i sent to you.

Thank you in advance.

[martinobermayr]

Hi @Anonymous 

Yep, unfortunately it still errors.

Your pbix has the same DAX as my pbix has - so the DAX itself should be correct.

I think the problem now is a performance problem: To simulate this, i added my salestable without privacy informations and i would like to ask you to try some of the family-filters on the left to see how long the calculation in the following columns takes time. (i send you the pbix with private message)

This example is filtered to one customer with 142 lines in the sales-table (out of 1.5 mio lines)

• Please at least have a try with the family filter "MULTISPRAY". There will be no new placement (which is correct), and the calculation of the 14 result lines lasts long.
• Please have a try with other family-filter as well.
• When setting no filter, the calculation takes > 3 minutes for just this one customer with 142 datasets.

Is there any chance to get the DAX much more performant?
Or is it a problem with the power query strings "customer+family" or "customer+family+yyyymm" that i implemented to get those filters to analyse new placements each customer?
Or is it because i have to get the family-id in the powerquery out of table products first?
Or will the only performance boost be to have those "new placement" tags already in the delivered database and only summarize them in PowerBI?