Costs for the days open in a month

[SuzieKidd]

Hi, I'm trying to work out how to calculate costs that were open for say 14 days in august and 25 days in september, how do I show the total monthly cost for the days open in august and september in power bi.  I have 2 tables, I have created a start date check so that if the costs started before 1st April 2023 then show that date else the actual start date, and I have another condtional column to check for the end date, so if the end is blank then show me 31st March 2024 else the atucal end date, and the start check date has a relationship with my calendar table date.

Thank you

[gmsamborn]

Hi @SuzieKidd 

My numbers seem to be off from yours so I added 'Date'[Date] to the columns after Month.

Click on the matrix.
Set "Drill on" to Columns.
Right-click on AUG.

Drill Down

This will show the daily detail for AUG.

You say that the range of AUG 5th to AUG 10th is 5 days whereas my count is 6 days.

Either the Start Date or the End Date would have to be ignored to match your numbers.

[Greg_Deckler]

@SuzieKidd Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[SuzieKidd]

Hi Greg,

Thank you for getting back to me, here is sample data in my Power BI Table

Customer ID	Cost ID	Start Date	End Date	Days Open	Daily Cost	Weekly Cost	Total Cost
123	85689	05/08/2024	10/08/2024	5	£459.36	£3,215.52	£2,296.80
123	96523	11/08/2024		234	£45.77	£320.39	£10,710.18
235	11253	01/04/2024	31/03/2025	365	£79.57	£556.99	£29,122.62
237	26598	01/04/2024	17/10/2024	159	£34.54	£241.75	£5,491.18
237	41125	18/10/2024	31/03/2025	165	£35.77	£250.39	£5,902.05
235	68895	01/04/2024		366	£262.38	£1,836.64	£96,030.02

Where the costs started before the financial year start, I have changed the date to 1st April 2024 otherwise it's just the start date, where the cost is still open at the financial year end, I have changed the date to 31st March 2025 otherwise it's just the end date. What I need to see is the costs for each month that the costs was open for, so where the cost is open for 5 days in August I want to see £2296.80, where a cost is open for the whole financial year I want to see the cost for that month so 30 days in Apr, Jun, Sep and Nov and 31 days for the rest

Customer ID	Cost ID	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov
123	85689					£2296.80
123	96523					£9187.20	£13780.80	£14240.16	£13780.80
235	11253	£2387.10	£2466.67	£2387.10	£2466.67	£2466.67	£2387.10	£2466.67	£2387.10
237	26598	£1036.20	£1070.74	£1036.20	£1070.74	£1070.74	£1036.20	£1070.74
237	41125							£465.01	£1036.20
235	68895	£7871.40	£8133.78	£7871.40	£8133.78	£8133.78	£7871.40	£8133.78	£7871.40

I hope this makes sense.

Thank You

Suzie

[gmsamborn]

Hi @SuzieKidd 

Would a measure like this help?

Period Cost = 
VAR _CostStartDate = 
    CALCULATE( 
        MIN( 'FactTable'[Start Date] ), 
        ALL( 'Date' ) 
    )
VAR _CostEndDate = 
    COALESCE(
        CALCULATE( 
            MAX( 'FactTable'[End Date] ), 
            ALL( 'Date' ) 
        ),
        MAX( 'Date'[Date] )
    )
VAR _DailyCost = 
    CALCULATE( 
        MAX( 'FactTable'[Daily Cost] ), 
        ALL( 'Date' ) 
    )
VAR _Result = 
    SUMX(
        FILTER(
            'Date',
            'Date'[Date] >= _CostStartDate
                && 'Date'[Date] <= _CostEndDate
        ),
        _DailyCost
    )
RETURN
    _Result

Let me know if you have any questions.

(I forgot to mention I created a Customer dimension table iin Power Query.  It ended up not being required.  I also used a date table that has Fiscal Periods.)

open days.pbix

[SuzieKidd]

Thank for you for getting back to me, sorry I may be being a bit dense but what is 'Date' in ALL, is this calendar date?  I'm unable to access the pbix access is blocked.

[gmsamborn]

Hi @SuzieKidd 

The date in ALL() is from a date table.

Does you company block downloads?

[SuzieKidd]

Thank you. 

Yes looks like they do block downloads.