cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BillyButcher
New Member

Sum for last days of month per each category - how to simplify

Hello, 

 

I have a quite peculiar problem. I have a column with values that represents the state of Inventory for each Site (category). 
Which means that the most recent one value for each month is always last day per each site per month. 

Example

for site 667 for november its going to be value 5 252 235.74 (31/12/2021) but for site 200 its going to be 79 967 894.18 (30/12/2021)

image_2022-06-14_203943006.png

 

The sum of those values should be 85 220 129.92 which is state of inventory for those two sites per december. 

I was able to calculate this with this measure: 

 

 

 

Inventory Cost = 
VAR _pretable =
    ADDCOLUMNS (
        SUMMARIZE (
            v_factinventorytransactions,
            v_dimdate[DateId],
            v_factinventorytransactions[SiteId]
        ),
        "InventoryCost", CALCULATE ( AVERAGE ( v_factinventorytransactions[RunningCost] ) )
    )
VAR _table =
    FILTER (
        _pretable,
        VAR _MaxDate =
            CALCULATE (
                MAX ( v_factinventorytransactions[InventoryTransactionDateId] ),
                ALLSELECTED ( v_dimdate[DateId] )
            )
        RETURN
            v_dimdate[DateId] = _MaxDate
    )
RETURN
    SUMX ( _table, [InventoryCost] )

 

 

 

 

Which works perfectly but I'm wondering if it can be simplyfied. I want it to simplify, because when I want to use this measure inside another one that sums those Inventory Cost values per month for last 3 months and I have wrong answers. 
Which means that this Inventory Cost measure works but if I call out this measure in the one below it shows wrong numbers (but other measures, more simply ones work). 

 

 

 

Rolling3Months = 
VAR _EndDate = MAX(v_dimdate[Date])
VAR _Dates =  DATESINPERIOD(v_dimdate[Date], _EndDate, -3, MONTH)
VAR _Cost = [Inventory Cost]
VAR _Inventory = SUMX(_Dates, CALCULATE(_Cost, ALL(v_dimdate[YearMonth])))
RETURN 
_Inventory 

 

 

 

 

I'm a little bit stuck and would be super appreciated when someone would pointed out my mistakes/errors here.

I'm also providing sample power BI file with those. 
https://we.tl/t-eQSOYHm1ft

Thank you 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please try the below measure.

I also attached the pbix file.

 

 

Rolling3Months =
VAR _EndDate =
    MAX ( v_dimdate[Date] )
VAR _months =
    SUMMARIZE (
        FILTER (
            ALL ( v_dimdate ),
            v_dimdate[Date] IN DATESINPERIOD ( v_dimdate[Date], _EndDate, -3, MONTH )
        ),
        v_dimdate[YearMonth]
    )
VAR _Inventory =
    SUMX ( _months, [Inventory Cost] )
RETURN
    IF ( [Inventory Cost] <> BLANK (), _Inventory )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

Hi,

Please try the below measure.

I also attached the pbix file.

 

 

Rolling3Months =
VAR _EndDate =
    MAX ( v_dimdate[Date] )
VAR _months =
    SUMMARIZE (
        FILTER (
            ALL ( v_dimdate ),
            v_dimdate[Date] IN DATESINPERIOD ( v_dimdate[Date], _EndDate, -3, MONTH )
        ),
        v_dimdate[YearMonth]
    )
VAR _Inventory =
    SUMX ( _months, [Inventory Cost] )
RETURN
    IF ( [Inventory Cost] <> BLANK (), _Inventory )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors