Get all charges between two dates

[kattlees]

I have two tables - payroll and charges.

Payroll has pay date, payroll start date, payroll end date, total pay

Charges has service date, amt charged.

What I need to do is choose a payroll date - let's say Aug 1 - which would have a start date of July 16 and an end date of July 31

I need to then display
Total pay for the Aug 1 pay date
Total charges with a service date that fell between July 16 and uly 31

I do have a calendar table with all dates in it but when I choose a date I only get charges for the date I pick.

[v-xicai]

Hi  @kattlees ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai · Accepted solution]

Hi @kattlees ,

You may create measure like DAX below.

Total charges= 
VAR start= SELECTEDVALUE(Payroll[payroll start date])
VAR end= SELECTEDVALUE(Payroll[payroll end date])
RETURN
CALCULATE(SUM(Charges[amt charged]),FILTER(ALLSELECTED(Charges),Charges[service date]>=start&&Charges[service date]<=end))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[kattlees]

Greg - I was trying to follow these instructions here and I can't seem to get it to work.

I do have a calendar table that just has Date in it.

I have the Payroll Column that has Pay Date, Pay Period Start and Pay Period End

I have Charges table that has ChgServDate and ChgAmt

Both Payroll and Charges are connected to Calendar table. With Date connected to Pay Date in Payroll table and Date connected to ChgServDate in Charges table.

In the tutorial you gave it looks like the start and end dates are in the charges table. 

Am I missing something as I feel totally confused.

My ultimate goal is

Pay Date            Pay Period Start              Pay Period End             Total Charges

7-11-19                  6-23-19                          7-06-19                       $8578253.32

7-25-19                  7-07-19                          7-20-19                       $7825678.47

[Greg_Deckler]

Ah, I may have steered you in the wrong direction. What you likely need is my Time Intelligence the Hard Way measure:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Basically, you should be able to create a table visualization with Pay Date, Pay Period Start and Pay Period End columns in it. Then all you would need is a measure that did something like:

Total Charges = 
VAR __payStart = MAX('Payroll'[Pay Period Start])
VAR __payEnd = MAX('Payroll'[Pay Period End)
VAR __table = FILTER(ALL('Charges'),[ChgServDate]>=__payStart && [ChgServDate] <= __payEnd)
RETURN
SUMX(__table,[ChgServDate)

Should be something along those lines.