I have an excel table the tracks daily hours and calculates pay. The legacy system I get the post records from. I want to summarize the current period daily pay from excel using a date filter against my 2019 payroll cutoff table.
I am not sure how to develop the calculate statement specifically the filter. Without slicer based upon today’s date
Calc below is not correct trying to work through this
Current Payperiod $ = CALCULATE(
SUM(Main[Daily Pay]),
FILTER(ALL(Cal),
Cal[Date] > LASTDATE('2019 Pay Period'[StartDate].[Date])) )
This important this here that you haven’t added it how are you looking to showcase this result? For example what will the context be for this calculation
Will it just be in a card?
You need to make sure you iterate through a date table, not the Cal table.
This is very important. Anything around dates should always been done with a date table.
Then you can grab the cut off date inside a variable somehow, and feed this into the the filter that should look like this
FILTER( ALL( Dates), DateColumn < CutOffDate Variable)
This is how I would do this.
How you want to show this though is important as the solution could be different depending on this.
Purpose is to add value to labor cost measure on a different page that keeps is value. I create measure on another page using a slicer
Current Payperiod $ =
CALCULATE( SUM(Main[Daily Pay]),
FILTER(ALLSELECTED(Cal[Date]),
Cal[Date] <= MAX(Cal[Date])))
The issue is it becomes unfiltered on other page. Would prefer to eliminate need for slicer by evaluating current date and identify current period start and cutoff date
Cal is the date table
Somehow you will need something to filter what cut off date you require. How do you intend to do this on your report?
This seems like the final piece to get right for this to work