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 ...
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.

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.

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

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