Hi,
I am creating a power BI report but I can't seem to post to the Power BI forum so this is the next best.
I have a client table with rows for each week and a number of hours invoiced. Due to clients joining and leaving, I obviously don't have a row for each client for each week.
I also have a date table.
I have a filter on the report for a date range and what I want to do is create a measure which will show the increase or decrease for each client based on the filtered date range.
I have uploaded a spreadsheet to provide an example of my data and what I want to achieve.
The problem I have is that where I don't have an invoice for the first week in the period, I want my start value to be zero, I don't want the value from the first invoice that is in the period. Similarly for the end of the period, no invoice, I want my end value to be zero. The measure needs to return end value - start value based on the dates in the range in the filter.
The spreadsheet contains a sample of data, column H shows the values that I want.
I am struggling with the measure in that I keep picking up the value of the first or last invoice for each client when what I really want is the value of the invoice for the start and end dates in my filtered range.
Hope that makes sense and somebody can help
There is no attachment. Please try again and don't forget to press "Start upload" before you submit your reply.
Attached file
I don't really understand your intentions based on the example you provided and, thus, can not say what DAX measure you would need. If the formulas in H18:H21 have any relevance to your question, please clarify. For instance, client 1 has invoices for each of the 5 weeks totaling 804 hours. Why do you calculate 175 - 167 = 8? What does that number mean? It's just the difference between the last and the first invoice between the two dates. Or are these perhaps cumulative hours billed to-date? If so, why is there a drop from 175 to 112 in week 27/10?
Riny, thank you for looking at this for me, I probably didn't explain my problem very well but I have now managed to get my DAX working
OK!
I have attached a Power BI file. My diff measure works as I want it to but I don't understand why my Diff2 measure doesn't work
I see what you mean but can't explain it. Perhaps someone else can.
Hi Ian,
The DAX FILTER function's second argument must return a Boolean true or false value.
This: Hours[End of Week] = [Start Date]
Returns a numeric value i.e. a date minus a date.
Mynda
Your Start Date and End Date measures are being calculated for each row of the table in the FILTER, so they will return true for all rows within the time period. That means that you get the same totals for both start and end hours. The simple fix is to store the measure values in variables at the start:
