SUMX + CALCULATE not working

[Alessandro_Scal]

Hi Everyone,

I am struggling with the following code that apparently seems quite simple:

Test sumx =

        SUMX(

            'Database',

        CALCULATE(

        SUM('Database'[Sales])/SUM('Database'[Quantity]), PREVIOUSMONTH('Calendar'[Date].[Date])

    )

        )

Basically, I'd like to compute the AVG price of the previous month, but I want also the results to be all summed up at every level (Customer/Item no./Business Unit). I know it does not make sense to sum up the AVG price, but this is part of a bigger code that I have already checked.

The formula doesn't seem to work, all I get is zeros.. but I am having an hard time figuring out why.. Here below I am attaching a snapshot of the Database I am working on.

Thank you!

Alessandro

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Alessandro_Scal]

Thanks, for the advice.. reposting with a proper data structure..

Hi Everyone,

I am struggling with the following code that apparently seems quite simple:

Test SUMX and CALCULATE =

 SUMX(

    Test,

    CALCULATE(

        SUM(Test[Sales])/SUM(Test[Quantity]),PREVIOUSMONTH(Test[Invoice date].[Date])

    )

 )

Basically, I'd like to compute the AVG price of the previous month, but I want also the results to be all summed up at every level (Customer/Item no./Business Unit). I know it does not make sense to sum up the AVG price, but this is part of a bigger code that I have already checked.

The formula doesn't seem to work, all I get is zeros.. but I am having an hard time figuring out why.. 

This is the database (first 10 rows):

Item no.	Invoice date	Month	Year	Invoice no.	Business Unit	Product type	Customer name	Sales	Quantity
2661	11/12/2023	12	2023	3078	Business Unit A	Product A	Customer 1	26.681,70	23.405,00
3346	09/11/2023	8	2023	2181	Business Unit A	Product A	Customer 2	9.367,64	7.391,00
3346	28/12/2023	2	2023	420	Business Unit A	Product A	Customer 2	5.044,34	4.234,00
3397	31/12/2023	1	2023	134	Business Unit A	Product A	Customer 3	2.528,15	921,00
3372	13/11/2023	4	2023	825	Business Unit A	Product A	Customer 3	3.962,09	1.991,00
3370	09/11/2023	5	2023	1113	Business Unit A	Product A	Customer 3	4.641,39	3.049,00
3370	21/12/2023	2	2023	380	Business Unit A	Product A	Customer 3	3.909,62	2.734,00
2105	11/11/2023	5	2023	1158	Business Unit A	Product A	Customer 4	1.534,00	1.300,00
2105	15/12/2023	9	2023	2249	Business Unit A	Product A	Customer 5	3.555,20	2.020,00

This is the outcome:

Any help?!

Thank you!

Alessandro

[lbendlin]

PREVIOUSMONTH and all the other Time "Intelligence"  functions require a proper calendar table. 

[Alessandro_Scal]

Hi,

thanks for answering. I have taken your pbix file and only sliced it differently, but I keep not getting the SUMX of previous period AVG price. Please see below the screenshots, as you can see when I run the formula on current AVG price it works fine, while when I do it on previous AVG price it returns only blank values. Any clue why this is happening? Thanks!

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Alessandro_Scal]

Here's is the pbix file and the expected output (in red).

Thanks

https://drive.google.com/file/d/19qXI6-giQpKrmzmFWLnxzsGrFYygXW4S/view?usp=sharing 

[lbendlin]

Link asks for access, please verify

[Alessandro_Scal]

Now it should work.. thanks

[lbendlin]

SUMX of prev Avg = CALCULATE( SUMX( 'Table',[Avg]),DATEADD('Calendar'[Date],-1,MONTH))