Calculate cost per day

[Anonymous]

I have a basic dataset with monthly cost. It only has month and year as a date column, so to calculate cost per day I could to Cost per day = [Total Cost] / 30 (very crude) but this only works if I include the month in the visual.

Ideally I want to ensure that even if I am not using the month in the visual, I can show Cost per day without it aggregating up.

Also, ideally I want to use the appropriate days of the month. 

Any ideas?

[Greg_Deckler · Accepted solution]

@Anonymous Really need sample data and a better understanding of the scenario. You can get the actual days in a month by constructing a DATE and then using EOMONTH with DAYS like the following (assuming your month is a numeric month:

DAY(EOMONTH(DATE([Year],[Month],1),0))

[Anonymous · Accepted solution]

Hmm, you could create a measure, that counts the number of different months in your table.

If you include month in your table, it will return 1. If you only year in your table, it will return 12 (under the condition, that you have data in each month)

Now that you have the number of months, you can do: DailyCost= Divide(TotalCost, NumberOfMonths *30)

[Greg_Deckler · Accepted solution]

@Anonymous Really need sample data and a better understanding of the scenario. You can get the actual days in a month by constructing a DATE and then using EOMONTH with DAYS like the following (assuming your month is a numeric month:

DAY(EOMONTH(DATE([Year],[Month],1),0))

[Anonymous]

Thanks guys, actually both these solutions work for me.