sum of worked hours between two dates in one column (billing period)

[MoKi]

Hello together,

my aim: Every 23rd of a month I want to have the sum of worked hours, I have to bill. My billing period goes from the 24th of one month to the 23th of the second month. And I just want to have the sum of worked hours in this time. I have one fact table with my worked hours and the Date and I have created a separate Datetable. But I have no idea how to create this billing period? 

Thanks a lot in advanced for your help! 

[amitchandak]

Make sure you have date calendar and you are filtering on 23 rd (selected in slicer)

Rolling 1 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-1,MONTH))  
or
Rolling 1 = CALCULATE(sum(Sales[Sales Amount]),all(date),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-1,MONTH))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[MoKi]

Hello @amitchandak  thanks for your reply a lot. How can I filter on the 23rd in general? When I´m using the slicer I can just filter the 23rd for a special month. The formula works in general. But now I have to integrate my period 24th of one month to 23rd of next month. Thanks a lot in advanced!

[amitchandak · Accepted solution]

Check the screenshot, In my calendar table, I created a day and filter the report on 23.

rolling 1 = CALCULATE(sum(Sales[Net Sales]),all('Date'),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-1,MONTH))
rolling 1 before 1 = CALCULATE(sum(Sales[Net Sales]),all('Date'),DATESINPERIOD('Date'[Date],MAXx(Sales,DATEADD(Sales[Sales Date],-1,MONTH)),-1,MONTH))

[MoKi]

Thank you, now I understand - this worked.

[Anonymous]

provide sample data and expect output, it will help us to answer your question.

Thanks,

Pravin