Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I haven't written DAX in a while and I'm having a bit of a hard time putting this together and I am hoping someone could throw in a suggestion.
What I have:
Qty Table (a lot more months than January):
+----------+-----------+----------+ | Location | Date | LaborQty | +----------+-----------+----------+ | NY | 1/3/2017 | 41.024 | | NY | 1/4/2017 | 33.836 | | NY | 1/5/2017 | 20.431 | | NY | 1/6/2017 | 35.544 | | NY | 1/7/2017 | 0 | | NY | 1/9/2017 | 33.337 | | NY | 1/10/2017 | 41.799 | | NY | 1/11/2017 | 70.469 | | NY | 1/12/2017 | 35.514 | | NY | 1/13/2017 | 31.573 | | NY | 1/15/2017 | 0 | | NY | 1/16/2017 | 22.041 | | NY | 1/17/2017 | 30.518 | | NY | 1/18/2017 | 47.576 | | NY | 1/19/2017 | 29.53 | | NY | 1/20/2017 | 18.155 | | NY | 1/21/2017 | 0 | | NY | 1/23/2017 | 31.284 | | NY | 1/24/2017 | 27.695 | | NY | 1/25/2017 | 38.907 | | NY | 1/26/2017 | 16.289 | | NY | 1/27/2017 | 30.976 | | NY | 1/28/2017 | 0 | | NY | 1/30/2017 | 21.434 | | NY | 1/31/2017 | 16.49 | +----------+-----------+----------+...etc
Rates Table:
+----------+-----------+------------+-----------+---------+-----------+--------+ | Location | DateFrom | DateTo | MonthFrom | MonthTo | RateType | Amount | +----------+-----------+------------+-----------+---------+-----------+--------+ | NY | 1/1/2017 | 6/30/2017 | 1 | 6 | LaborRate | 129.7 | | NY | 7/1/2017 | 9/30/2017 | 7 | 9 | LaborRate | 129.8 | | NY | 10/1/2017 | 12/31/2017 | 10 | 12 | LaborRate | 129.9 | | DC | 1/1/2017 | 6/30/2017 | 1 | 6 | LaborRate | 130.1 | | DC | 7/1/2017 | 9/30/2017 | 7 | 9 | LaborRate | 130.5 | | DC | 10/1/2017 | 12/31/2017 | 10 | 12 | LaborRate | 130.7 | +----------+-----------+------------+-----------+---------+-----------+--------+
Desired type of output for the month (e.g. LaborQty x LaborRate):
+-------+----------+-----------+------------+ | Month | LaborQty | LaborRate | Result | +-------+----------+-----------+------------+ | 1 | 674.22 | 129.74 | 87473.3 | | 2 | 350 | 129.74 | 45409 | | 3 | 375 | 129.74 | 48652.5 | | 4 | 400 | 129.74 | 51896 | | 5 | 380 | 129.74 | 49301.2 | | 6 | 500 | 129.74 | 64870 | | 7 | 550 | 129.76 | 71368 | | 8 | 600 | 129.76 | 77856 | | 9 | 675 | 129.76 | 87588 | | 10 | 700 | 129.98 | 90986 | | 11 | 780 | 129.98 | 101384.4 | +-------+----------+-----------+------------+
What I am trying to write:
A DAX measure that will output amount like the one shown in the result column. If I where to write a linq query for grabbing the correct rate it will look something like this:
LaborRate = db.Rates .Where(a => a.DateFrom <= SelectedDate & a.DateTo >= SelectedDate & a.RateType == "LaborRate") .Select(a => a.Amount).Sum();
I have tried a combination of CALCULATE, SUM, FILTER, RELATED, SUMX but I couldn't get it to work. Any advice would be much appreciated. What would be the simplest approach? My conditions/filters are: Location, Date >=DateFrom, Date<=DateTo, RateType.
Solved! Go to Solution.
Hi @IoanCosmin
Please see the attached file here. Hope it helps
Here are the steps
First I added a Caclulated Column in QtyTable
Month = MONTH ( QtyTable[Date] )
Second, a MEASURE in QtyTable to get the LaborRate for the months
LaborRate = CALCULATE ( VALUES ( RatesTable[Amount] ), FILTER ( RatesTable, SELECTEDVALUE ( QtyTable[Month] ) >= MONTH ( RatesTable[DateFrom] ) && SELECTEDVALUE ( QtyTable[Month] ) <= MONTH ( RatesTable[DateTo] ) && SELECTEDVALUE ( QtyTable[Location] ) = RatesTable[Location] ) )
Thirdly a MEASURE to get the RESULT
RESULTT = SUM ( QtyTable[LaborQty] ) * [LaborRate]
Hi @IoanCosmin
Please see the attached file here. Hope it helps
Here are the steps
First I added a Caclulated Column in QtyTable
Month = MONTH ( QtyTable[Date] )
Second, a MEASURE in QtyTable to get the LaborRate for the months
LaborRate = CALCULATE ( VALUES ( RatesTable[Amount] ), FILTER ( RatesTable, SELECTEDVALUE ( QtyTable[Month] ) >= MONTH ( RatesTable[DateFrom] ) && SELECTEDVALUE ( QtyTable[Month] ) <= MONTH ( RatesTable[DateTo] ) && SELECTEDVALUE ( QtyTable[Location] ) = RatesTable[Location] ) )
Thirdly a MEASURE to get the RESULT
RESULTT = SUM ( QtyTable[LaborQty] ) * [LaborRate]
Hello Zubair,
Thank you so much for putting everything together. This is so nice of you to help and create an example for me. I really appreciate your effort. This indeed solves my problem and it works great!
This is more out of curiosity... I have noticed that you cannot use SELECTEDVALUE in PowerPivot. It's a new function for PowerBI and I was wondering if there's something similar that might work as a replacement. I do no actually need it at this point.
Best,
C
Hello Zubair,
Thank you so much for putting everything together. This is so nice of you to help and create an example for me. I really appreciate your effort. This indeed solves my problem and it works great!
This is more out of curiosity... I have noticed that you cannot use SELECTEDVALUE in PowerPivot. It's a new function for PowerBI and I was wondering if there's something similar that might work as a replacement. I do no actually need it at this point.
Best,
C
Hi @IoanCosmin
Yes SelectedValue is a new function. Even I do not have it in Excel 2016
Instead of
Hi @IoanCosmin
A very good article on SELECTEDVALUE function by the champ @Marco Russo
https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |