Forum Discussion

New_be's avatar
New_be
Icon for Helper V rankHelper V
5 years ago
Solved

Calculate values between 2 dates & between 2 different month

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!

Need Help
Show and Tell
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

4 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
      New_be
      Icon for Helper V rankHelper V
      5 years ago
      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's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        5 years ago

        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.