Calculate running totals and assign a default category value based on max Trans date

[Anonymous]

Hi guys ,

This is my first post on here , so i hope i can explain my issue the best way i can .

Sample Data Set:

inventory Table

S/N	Itemid	Amount	Category	PostingType	Transdate
1	10000	$    25.00	A	1	1/1/2020
2	10001	$    25.00	B	1	1/15/2020
3	10000	$    25.00	B	1	1/15/2020
4	10001	$    25.00	A	2	1/31/2020
5	10000	$    25.00	C	2	1/31/2020
6	10000	$    25.00	B	1	2/1/2020
7	10001	$    25.00	A	1	2/15/2020
8	10001	$    25.00	C	1	2/15/2020
9	10000	$    25.00	A	1	2/26/2020
10	10000	$    25.00	D	2	2/28/2020
11	10001	$    25.00	C	2	2/28/2020

I am trying to assign the correct 'Category' for  an item (every month)  based on the following conditions ;

1) Transdate is month end 

2)  Posting Type value  = 2

Based on the conditions above. If the logic is correct, I should have the result in green below:

I want to be able to dynamically select any month in a slicer and get the result above;

i have been able to calculate running totals  using the dax below , however calulating the category (in a measure)  has been difficult .However when i introduce the category field , i get the wrong  value above .

i am wondering if there is way to recalculate the category field to get the desired result 

TotalValue:= SUM('table'[Amount])


CurrentValueRT:=

VAR MAXDATE = MAX('table'[TransDate])
VAR RESULT =
CALCULATE( 'table'[TotalValue],

'table'[TransDate]<= MAXDATE,
ALL ('table'[TransDate])
)
RETURN
RESULT

Hopefully this makes sense and any help much appreciated.  i am not an expert and was trying to use a calculated column but i believe a measure might work better 

Thanks in advance 

Regards,

Ola

[Anonymous · Accepted solution]

I was able to resolve this using the dax below:

Total Amount =
VAR __cat = MAX( 'Table'[Category] )
VAR __summary =
CALCULATETABLE(
ADDCOLUMNS(
ADDCOLUMNS(
SUMMARIZE(
'Table',
'Table'[ITEMID],
'Table'[WarehouseID]
),
"@amt", CALCULATE( SUM( 'Table'[Amount] ) ),
"@maxdate", CALCULATE( MAX( 'Table'[TRANSDATE] ) )
),
"@Cat",
VAR __dt = [@maxdate]
RETURN
CALCULATE(
MAX( 'Table'[Category] ),
'Table'[TRANSDATE] = __dt
)
),
ALLSELECTED( 'Table'[Category] )
)
RETURN
IF(
__cat IN SELECTCOLUMNS( __summary, "_cat", [@Cat] ),
SUMX( FILTER( __summary, [@Cat] = __cat ), [@amt] )
)

[Anonymous]

This measure works very well  when calculating the cummulative totals ,

However the cummulative totals are calculated at monthly levels not at daily levels. (any ideas on how this can be fixed)

Totalvalue2 =
SUMX (
FILTER (
ALL ( ' SAMPLE DATA' ),
[Itemid] IN DISTINCT ( 'SAMPLE DATA'[Itemid] )
&& [WarehouseID] IN DISTINCT ( 'SAMPLE DATA'[WarehouseID] )
&& [Transdate] <= SELECTEDVALUE ( 'SAMPLE DATA'[Transdate] )
),
[Amount]
)

[v-janeyg-msft]

@Anonymous 

You need to modify depending on your context.

[Anonymous]

Hi Thanks for this however i couldnt solve the answer based on the query above .

thanks again

[v-janeyg-msft]

@Anonymous 

For the new things you mentioned later, I can't see where your questions and needs are, and I can't see the needs from your files. And It has almost nothing to do with your original problem. I asked you but didn't say anything. How can I help you?

[Anonymous]

Hi @v-janeyg-msft , 

thanks for your response ,  the original question was based off a logic & sample dataset  i tried to implement which didnt work .

you requested for a wider data set which reflects the real problem which i have created 

i will put the question below again

Below is a drop box link with a sample dataset :

https://www.dropbox.com/scl/fi/8cqz36yc44brmf2rzre7w/MATDW-SAMPLE-DATA_v2.xlsx?dl=0&rlkey=jzgswrfhet...

I will explain my challenge from scratch to help you understand better:

The Summary of my challenges is :

The (Running total) "Total Amount" for an item should be assigned to the most recent category based on the Transdate (as filtered by the slicer) .

The Total amount (Business Definition):

The sum of All prior sales of an item to date ( Transdate selected in slicer) .

The Category of an item (Business Definition) :

The category of the item should be the most recent category based on the transdate (as filtered by the slicer)

i have also attached a Sample answer calculated from the Excel sheet

I am also looking to get (Totals by category) "calculated categories above" for every warehouse :

NB: Totals might not be correct.

[v-janeyg-msft]

@Anonymous 

Sorry, due to the nature of work, we can't reply to anything outside of the post.
I asked questions because you didn't provide anything that you operate in powerbi, but just describe your needs. The forum will give you help in the process of making the pbi report, but if you want me to help you make a complete report, it’s a bit difficult.

So the best way is to share a sample pbix file of yours, and then describe the problems you encountered in it.

If it doesn't work, you still need to answer my question first, and then I will help you based on my understanding, but it is difficult to get it right in one step.

Reference:How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,
Community Support Team _ Janey

[Anonymous]

Thanks for your help :

i have been able to calculate  the right categories & totals by tweaking the dax queries as show  below : 

this works well  as shown below ;

Measure =
VAR a =
MAXX (
FILTER (
ALL ( 'SAMPLE DATA' ),
MONTH ( [Transdate] ) = MONTH ( SELECTEDVALUE ( 'SAMPLE DATA'[Transdate] ) )
),
[Transdate]
)
RETURN
IF (
SELECTEDVALUE ( 'SAMPLE DATA'[Transdate] ) = a
,
1,
0
)

---------

Totalvalue2 =
SUMX (
FILTER (
ALL ( ' SAMPLE DATA' ),
[Itemid] = SELECTEDVALUE ( 'SAMPLE DATA'[Itemid] )
/* && [Category] = SELECTEDVALUE ( 'SAMPLE DATA'[Category] ) */
&&[WarehouseID]= SELECTEDVALUE ( 'SAMPLE DATA'[WarehouseID] )
&& MONTH ( [Transdate] ) <= MONTH ( SELECTEDVALUE ( 'SAMPLE DATA'[Transdate] ) )
&& YEAR ( [Transdate] ) <= YEAR ( SELECTEDVALUE ( 'SAMPLE DATA'[Transdate] ) )
&& [Transdate] <= SELECTEDVALUE ( 'SAMPLE DATA'[Transdate] )
),
[Amount]
)

However there are two main  issues :

1.)when i select an older transdate, the  totalvalue & categories become blank

2) when i try to total value by calculated  category , if an item is not selected the "calculated category groupings " dont filter the table 

[v-janeyg-msft]

Hi, @Anonymous 

I originally wrote this measure to filter row data based on your original question, but according to your current visual, this measure has no meaning at all...and it will cause errors after you use the date slicer, such as not displaying the data.   You don't need to put this measure in filter pane.

The logic here is a bit messy, can you describe the date range in text? Let me see if there is any problem.

[Anonymous]

the date range is july 2009  till date

[v-janeyg-msft]

@Anonymous  According to your logic, You only need to use:

[Transdate] <= SELECTEDVALUE ( 'SAMPLE DATA'[Transdate] )&&[Transdate]>=date(2009,7,1)

And  ALL ('SAMPLE DATA') is used above, if july 2009 is the smallest date in your data source. One condition is enough.

Like this:

Totalvalue2 =
SUMX (
    FILTER (
        ALL ( ' SAMPLE DATA' ),
        [Itemid] = SELECTEDVALUE ( 'SAMPLE DATA'[Itemid] ) /* && [Category] = SELECTEDVALUE ( 'SAMPLE DATA'[Category] ) */
            && [WarehouseID] = SELECTEDVALUE ( 'SAMPLE DATA'[WarehouseID] )
            && [Transdate] <= SELECTEDVALUE ( 'SAMPLE DATA'[Transdate] )
    ),
    [Amount]
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[Anonymous]

Thanks a lot , i must admit this has been a long road and i appreciate the effort in helping overcome the challenge .

The measure works at  getting individual item categories (calculated) and totals .(Item level) .

it is  dynamic based on the transdate  which is great news.

However i am also trying to return  totals by categories (Calculated categories) based on transdate selected.(summary data )

 when i select an item a single item  , it returns a value ( works properly)

Whenever i select "ALL" / multiple selections  it returns blank / wrong values for the totals and categories as shown below:

i would like to get values when i select  "ALL " & "Multiple selections" 

thanks,

Okubizzy

[v-janeyg-msft]

@Anonymous 

Modify it using dictinct instead of selectedvalue:

Totalvalue2 =
SUMX (
    FILTER (
        ALL ( ' SAMPLE DATA' ),
        [Itemid]
            IN DISTINCT ( 'SAMPLE DATA'[Itemid] ) /* && [Category]  in distinct ( 'SAMPLE DATA'[Category] ) */
                && [WarehouseID]
                    IN DISTINCT ( 'SAMPLE DATA'[WarehouseID] )
                        && [Transdate] <= SELECTEDVALUE ( 'SAMPLE DATA'[Transdate] )
    ),
    [Amount]
)

 DISTINCT (column) function (DAX) - DAX | Microsoft Docs

Best Regards,
Community Support Team _ Janey

[Anonymous]

This  is great work. 

This measure  works as expected when calculating totals  at end of month dates .

for instance : when selecting a transdate that is monthend ( 2021-10-31)  it returns cummlative totals .

however when a  transdate date that is not month end  date is selected ( 2021-10-13)  the measure performs unexpectedly.

it dosent return cummulative totals.

i am trying to get it working for any transdate  date selected in range .

[v-janeyg-msft]

@Anonymous 

Because there is no restriction on the date column in the measure, it just calculates the total amount that is less than this date based on the date column you choose.

If you want to calculate dates within a certain range, you can also modify the measure.

You need to change the date slicer into a relative date range and modify like this:

[Transdate] in distinct ( 'SAMPLE DATA'[Transdate] )

Best Regards,
Community Support Team _ Janey

[Anonymous]

This changes the result set (totals) as i am not trying to use a particular range.

I am just trying to calculate cummulative totals for any trans dates selected  in the slicer.

however the current measure only returns accurately when date selected is an End of month date

[v-janeyg-msft]

@Anonymous  

What's your measure?

[Anonymous]

Totalamount =
SUMX (
FILTER (
ALL ( 'Table' ),
[Itemid] in DISTINCT ( 'Table'[Itemid] )
&&[WarehouseID] in DISTINCT ( 'Table'[WarehouseID] )
&& [TransDate] <= SELECTEDVALUE ( 'Table'[TransDate] )
),
[Amount]
)

That is my measure for calculating cummulative totals .

also would it be possible  to calculate ytd values based on this logic?

[v-janeyg-msft]

@Anonymous 

There is no problem with the logic of the measure. Since you said that the result is wrong, Can you share the difference between the wrong result and the correct result? In this way, I can make a comparison to judge where I misunderstood.

It's ok. Depends on your needs.

[Anonymous]

wrong result :

When Trans date selected is not an end of month date as shown below  

Correct result when end of month date is selected as transdate as shown below

end of month value :

Thanks .