Total sum of a daily measure

[Anonymous]

I'm currently working on inventory reconciliation, and used this measure to calculate the amount of $s(product) we're holding by day:

Inventory = CALCULATE(SUM(ledger[cost]),
                      FILTER(ALL(DimDate[Date]),
                             DimDate[Date]<=MAX(DimDate[Date])))

Also, we get charged a fee based upon the amount dollars we're holding by day:

Fee = (0.03/365)*[Inventory]

This how a sample looks like:

Date	Inventory	Fee
1/1/2020 0:00	$15,912.00	$1.31
1/2/2020 0:00	$14,976.00	$1.23
1/3/2020 0:00	$7,020.00	$0.58
1/4/2020 0:00	$7,020.00	$0.58
1/5/2020 0:00	$7,020.00	$0.58
1/6/2020 0:00	$3,276.00	$0.27
1/7/2020 0:00	$3,276.00	$0.27
1/8/2020 0:00	$1,404.00	$0.12
1/9/2020 0:00	$1,404.00	$0.12

I've been trying to create another measure, which is going to be the total sum of the fee but so far I've been successful.

Thank you!

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[Anonymous]

Hi Allan,

it's done

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

ledger:

DimDae(a calculated table):

DimDate = CALENDARAUTO()

You may create measures as follows.

Inventory = 
IF(
    MAX(ledger[date])<>BLANK(),
    CALCULATE(
        SUM(ledger[cost]),
        FILTER(
            ALL(DimDate[Date]),
            DimDate[Date]<=MAX(DimDate[Date])
        )
    )
)

Fee = 
SUMX(
    ledger,
    (0.03/365)*[Inventory]
)

Finally you may use a table visual or a card visual to display the result.

Best Regards

Allan

If this post helps, then please consider Accepting it as the solution.

[amitchandak]

Try

Fee Sum= SUMX(all(DimDate), [fee])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Anonymous]

Thank you! 

[PaulDBrown · Accepted solution]

@Anonymous 

Try:

Sum of fee = SUMX(DimDate, [fee])

[Anonymous]

thank you!