Calculate values between 2 dates & between 2 different month

[New_be]

Hi Power Bi experts! 

Purpose of this question:

I need to calculate values between 2 dates based on payroll calculation. In my case for example this month is in Disember, they will pay starting date 23 Nov until 24 Dis. Same goes if this month is in November, they will pay starting from 23 Oct until 24 Nov.

Problem:

My problem is to calculate the values between that 2 dates because we want value of this month for example, but its also involve the previous month.

Desired Outcome:

When i click at month Disember (M12), it will show data from 23/11 - 24/12 and so on.

Below is my calendar table:

I try to solve this, but its doesn't work. Really need your guide. Thanks in advance!

[v-jingzhang · Accepted solution]

Hi @New_be 

You could use DATESBETWEEN function in a Measure to calculate the values you want. For example:

Monthly Sales = 
VAR endMonthNR = SELECTEDVALUE('Calendar'[MonthNR])
VAR startMonthNR = IF(endMonthNR = 1, 12, endMonthNR - 1)
VAR endYearNR = SELECTEDVALUE('Calendar'[YearNR])
VAR startYearNR = IF(endMonthNR = 1, endYearNR - 1, endYearNR)
RETURN
CALCULATE(SUM(Sales[Sales]),ALL('Calendar'),DATESBETWEEN('Calendar'[Date],DATE(startYearNR,startMonthNR,23),DATE(endYearNR,endMonthNR,24)))

I use SUM() in my example, you could replace it with other Aggregate function you need. Here is a sample PBIX file for it. Kindly let me know if this works.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[New_be]

Sorry to ask, but what is the meaning of this code? 

VAR endMonthNR = SELECTEDVALUE('Calendar'[MonthNR])

VAR startMonthNR =

IF(

endMonthNR = 1,

12,

endMonthNR - 1

)

[v-jingzhang]

@New_be In case you have data in multiple years, when you click at month January, it should show data from 23/12 (previous year) - 24/1 (current year), so I add code to change the startMonthNR to 12, otherwise it is (endMonthNR - 1). endMonthNR is the month you click at.

[New_be]

Ohhh i see.. Very smart! 😀
Thank you so much for your guide! Really appreciate it 😁