Current Month and YTD Calculation

[Anonymous]

I have three tables: Business Calendar, Cost, and Project with sample data and column structure provided below:

I want to create a measure to calculate monthly cost such that if today is between current month business day and next month business day, then calculate total cost for previous month. For example, today is between March business day, and next month business day, so calculate Feb cost for each project. Also, if I refresh data next month on April business day (and between this day and May business day), the cost should automatically reflect for the month of March. I also want to do the same for YTD. Any helps would be appreciated!

[saurabh_kedia_]

Hi Nick,

The below measure basically sums up all the cost as per current month i.e. business calendar based on your system date.

TOTAL_EXPENSE_TD = CALCULATE(SUM(COST[COST]),COST[MONTH]<MONTH(TODAY) && COST[YEAR]=YEAR(TODAY()))

Regards,

Saurabh Kedia

[v-chuncz-msft]

@Anonymous,

You may try to use RANKX Function and take a look at https://www.sqlbi.com/articles/lookup-multiple-values-in-dax/.