DAX SUM between Dates is not working as expected

Question

I have two tables:

DateDim
Time

I am trying to get the sum of hours_actual from my Time table where they are between two dates from my DateDim. They have a relationship on the date shown in the following:

I am currently using the following DAX formula:

PreviousPeriod_Hours = CALCULATE(SUM('Time'[hours_actual])
    ,DATESBETWEEN(
                  DateDim[FullDateAlternateKey],
                  [Start of Previous Period],
                  [End of Previous Period]),
                  ALL(DateDim)
                  )

The values for [Start of Previous Period] and [End of Previous Period] are calculated DAX dates, that are showing as I would expect.

In order to arrive at those dates I create a few DAX functions first:

Start of This Period = FIRSTDATE(DateDim[FullDateAlternateKey])

End of This Period = LASTDATE(DateDim[FullDateAlternateKey])

Days in This Period = DATEDIFF([Start of This Period],[End of This Period],DAY)

End of Previous Period = PREVIOUSDAY(LASTDATE(DATEADD(DateDim[FullDateAlternateKey],-1*[Days in This Period],DAY)))

Start of Previous Period = PREVIOUSDAY(FIRSTDATE(DATEADD(DateDim[FullDateAlternateKey],-1*[Days in This Period] + IF(MOD(Year('MeasureTable'[End of This Period]),4) == 0,1,0),DAY)))

To quickly summarize the above, it is finding the days between a start and end date, and then subtracting these days from my start and end dates that are selected. If it is a leap year, then add a day.

The dax formula is giving me the correct sum total I am expecting. However, if I display the hours by month between the 2 dates, they are showing something different altogether from what it should be, and don't add to the sum it displays.

I was expecting the following values:

I am not sure where the 13 is coming from, and the 28.25 looks to be a repeat from the previous month of the following year. What I am missing here? Is my current approach correct, I am just doing something incorrectly? or am I taking the wrong approach altogether?

UPDATE - Adding in some of the data I am working with:

Then the DateDim is just a generated date table, for example, a row looks like the following (2016-2021):

FullDateAlternateKey    Year    Month   Month Name  Quarter Week of Year    Week of Month   Day Day of Week Day of Year Day Name    Fiscal Year Fiscal Period Fiscal Quarter
2016-01-02              2016    1       January     1       1                1              2   6           2           Saturday    2016        5            2

And the hours_actual and date look like the following:

Date_Start              hours_actual

2019-03-05 12:00:00 AM  5
2019-03-26 12:00:00 AM  3
2019-04-23 12:00:00 AM  0.75
2019-04-24 12:00:00 AM  0.08
2019-05-22 12:00:00 AM  4
2019-05-22 12:00:00 AM  2
2019-05-22 12:00:00 AM  1.75
2019-05-27 12:00:00 AM  8
2019-05-31 12:00:00 AM  0.25
2019-06-03 12:00:00 AM  0.25
2019-06-05 12:00:00 AM  0.25
2019-06-21 12:00:00 AM  1
2019-06-27 12:00:00 AM  2
2019-06-27 12:00:00 AM  0.5
2019-06-28 12:00:00 AM  1
2019-06-28 12:00:00 AM  3
2019-07-04 12:00:00 AM  3
2019-07-05 12:00:00 AM  3
2019-07-10 12:00:00 AM  2.5
2019-07-10 12:00:00 AM  0.5
2019-07-10 12:00:00 AM  1.5
2019-07-10 12:00:00 AM  0.5
2019-07-10 12:00:00 AM  2
2019-07-12 12:00:00 AM  2.5
2019-07-17 12:00:00 AM  1
2019-07-18 12:00:00 AM  0.5
2019-07-24 12:00:00 AM  0.5
2019-07-24 12:00:00 AM  1
2019-07-24 12:00:00 AM  1.5
2019-07-24 12:00:00 AM  1
2019-07-25 12:00:00 AM  1
2019-07-25 12:00:00 AM  0.5
2019-07-31 12:00:00 AM  1
2019-07-31 12:00:00 AM  1.5
2019-07-31 12:00:00 AM  1
2019-07-31 12:00:00 AM  0.5
2019-08-01 12:00:00 AM  2
2019-08-07 12:00:00 AM  4
2019-08-07 12:00:00 AM  3.75
2019-08-08 12:00:00 AM  4
2019-08-14 12:00:00 AM  1.25
2019-09-11 12:00:00 AM  3.5
2019-09-11 12:00:00 AM  2.5
2019-09-12 12:00:00 AM  3
2019-09-12 12:00:00 AM  1.75
2019-09-13 12:00:00 AM  4
2019-09-13 12:00:00 AM  1.75
2019-09-13 12:00:00 AM  3
2019-09-14 12:00:00 AM  2
2019-09-14 12:00:00 AM  3.25
2019-09-16 12:00:00 AM  0.5
2019-09-16 12:00:00 AM  0.5
2019-09-26 12:00:00 AM  2.5

could you add code for [Start of Previous Period] and [End of Previous Period]. they might also be important — Nikolaj Klitlund Børty
Hi @NikolajKlitlundBørty I just added the details on the dates. Thank you! — ghawes
shouldn't your expected values be shifted one month. as i understand you are trying to calculated hours in the previous period. such that April will give you the March values ie. 8 — Nikolaj Klitlund Børty
Hi @NikolajKlitlundBørty - Your above comment would be correct if I change the slicer to look at one month. In the example above I am looking at one Fiscal year, Sept 01, 2019 -Aug.31, 2020. This places the previous hour's dates between Sept 01, 2018 - to Aug.31, 2019. The sum is presented correctly showing 74.33 hours worked in the 2019 Fiscal year, but presenting the values in that year by month is not showing correctly. I would have expected to see values between March-Aug 2019 — ghawes

ghawes ghawes · Accepted Answer · 2020-09-29T18:54:06

After experimenting a little more, the DAX functions for the previous start and end dates were being picked up on a monthly basis as well as a yearly basis. My mistake was thinking the DAX function would only evaluate on the slicers and not on table values presented.

I took a different approach, and basically created a reference table of the Time table, and added a column that added a year to the date for each row. I then joined the reference table to my DateDim table by this future_date column. I was finally able to show the values by the current period and previous period and it accurately gave the results I was looking for.

DAX SUM between Dates is not working as expected

1 Answers