Forum Discussion
Date Variable Price
Hello, I have the following data table;
| ID | Admission Date | Discharge Date | Duration | Current Cost |
| 1 | 30/01/2023 | 02/02/2023 | 3 | |
| 2 | 01/02/2023 | 10/02/2023 | 9 | |
| 3 | 31/01/2023 | 45 |
(ID 3 Does has not been discharged yet)
I would like to calculate current cost based off (Duration * Day Rate) using the following reference table
| Start Date | End Date | Day Rate |
| 01/01/2023 | 31/01/2023 | 5 |
| 01/02/2023 | 28/02/2023 | 10 |
Where Day Rate is variable, based on the Admission and Discharge Date
Could someone please help me with the appropriate DAX formula / solution to this problem?
Thanks
1 Reply
Anonymous , You need a date table with start date of month and end date of month
date = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)then use measures given video or blog for between range
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
