How to get total in new column independent from selected date range?

[TM_AC]

Hi,

this is my data:


and this is my current PowerBI report:

The problem is the calculated column 'TotalQty'. It sums up the quantity of all items in each row, bit it should only sum up the quantity for the item inside the row.

For TotaIQty I created a calculated column with follwing formula:

TotalQty = CALCULATE(

SUM(salesfact[Sold qty.]),

ALL(salesfact[Posting date]),

FILTER(

salesfact, salesfact[Posting date] >= DATE(2022,1,1) && salesfact[Posting date] <= DATE(2022,12,31)

)

)

My idea was to remove the filter on 'Posting date' (set by the slicer on the left) and set a new date range as I wish.

But it doesn't work, for some reason. The total is calculated for all items and not row by row.

What I am missing please?

[v-kkf-msft]

Hi @TM_AC ,

Please try the following formula:

TotalQty_M = 
IF (
    SUM ( salesfact[Sold qty.] ) <> BLANK (),
    CALCULATE (
        SUMX ( salesfact, salesfact[Sold qty.] ),
        ALL ( calendartable[Year] ),
        FILTER (
            ALL ( salesfact[Posting date] ),
            salesfact[Posting date] >= DATE ( 2021, 1, 1 )
                && salesfact[Posting date] <= DATE ( 2021, 12, 31 )
        )
    )
)

Or

Measure = 
CALCULATE (
    SUM ( salesfact[Sold qty.] ),
    ALL ( calendartable[Year] ),
    ALL ( salesfact[Posting date] )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TM_AC]

@PC2790 thanks, I also thought about that kind of solution, but I really wonder if it is really necessary to create a new table to get what I want.
There are built in functions like SAMEPERIODLASTYEAR which do similar things without an additional table.

So please, if anybody has a solution wihtout an additional table I really would appreciat that.

[TM_AC]

Hi,

I could get one step further. I switched now from calculated column to a measure.
This is my formula for 'TotalQty_M' now:

TotalQty_M = CALCULATE(

SUMX(salesfact, salesfact[Sold qty.]),

ALL(calendartable),

FILTER(

salesfact, salesfact[Posting date] >= DATE(2021,1,1) && salesfact[Posting date] <= DATE(2021,12,31)

)

)

Which will show:

As we can se here for year 2021 and month set to FEB the formula calulates 'TotalQty_M' correctly now line by line and also gives the correct total.

But if I select year 2022 and keep month at FEB I get this:
But 'TotalQty_M' is empty for all lines. It seems my formula does not remove the date filter.
In my model the 'Posting date' of salesfact table is linked to 'Date' from my calendartable.
Therefore I used

ALL(calendartable),

in my formula to get rid of any datefilter. But for some reason that is not working.
The selected filter in both sliders seems still to be active.

How can I get the formula right to remove the date filters (set by the slicers) for the calculation?

[PC2790]

You are not getting the data for 2022 as you are explicitly mentioning the condition to take into account 2021 dates:

FILTER(

salesfact, salesfact[Posting date] >= DATE(2021,1,1) && salesfact[Posting date] <= DATE(2021,12,31)

)

So that's why I suggested to use ALL(Posting Date) rather than this Filter. 

[TM_AC]

@PC2790, thanks again but I don't understand.
In 'TotalQty' I don't want to get the data for 2022 but for 2021 as set by the filter.
'Sold qty' should show the sold qty. based on the slicers. And 'TotalQty' should ignore the slicers and calculate (row by row) the total over the complete year 2021.

Using this formula (in a calculated column):

TotalQty = CALCULATE(

SUM(salesfact[Sold qty.]),

FILTER(

ALL(salesfact[Posting date]),

salesfact[Posting date] >= DATE(2021,1,1) && salesfact[Posting date] <= DATE(2021,12,31)

)

)

Gives the correct result, but it does not remove the filters set by the slicers.
And because of this if slicers are set to 2022 and FEB 'TotalQty' is empty.

I really don't understand why the filters from the slicers are not removed even if I use

ALL(salesfact[Posting date])

for my understanding this should remove the filters on 'Posting date' coming from the slicers - or not?

[PC2790]

I now understand your requirement.

So what I would do is create a new disconnected table which will contain a calculate column with the Quantity across 2021 and then you can use that column in the table visualisation which would not change when selected anything form the slicer.

See my example below:

ReqdSales is my column from Disconnected Table and contains sales for 2018.

If you see it is not getting updated on slicer selection, though the one with ALLSALES is getting updated.

My Data model as below with MySales table being disconnected from the Star Schema

[TM_AC]

@SanketBhagwat : thanks, I tried what you suggested:


but unfortunately it makes no difference at all.

@PC2790: thanks, I tried what you suggested:

here also no difference. 

BTW, this is what I expect if Month 1 (January) is selected (based on the data I presented in my first post of this thread):
(column TotalQty I added manually for demonstrating purposes using MS Paint)


Important here is that the total of column TotalQty also must be correct.
Normally if we sum up TotalQty for all 3 rows it would give 18+27+27 = 72.
But correct would be 45 since last 2 rows are the same item and should be considered only once in the total.

How I can get there now?

[PC2790]

See if this works:

AllQty = CALCULATE(SUM(salesfact[Sold qty.]),ALL(salesfact[Posting date]),All(salesfact[Customer No]),All(salesfact[Item No]))

This should give you the desired result.

[SanketBhagwat]

Hi @TM_AC 
Try SUMX instead of SUM and let me know if it works.

Thanks,
Sanket

If this post helps, then mark it as 'Accept as Solution' and give it a thumbs up.