Forum Discussion

dbrandone's avatar
dbrandone
Helper IV
5 years ago
Solved

Last Month Calculations not functioning

Hey everyone,

 

I have the below measure that I am trying to calculate, but I cannot figure out why the dates between section is not filtering the data. The result is showing "Blank", but according to the data in the table view, I should have hundreds. I checked the relationships and the column formats and they are set correctly to date/time. 

 

Items sold Last Month =
                VAR vToday =
                         TODAY ()
                VAR vEndDate =
                         EOMONTH ( vToday, -1 )
                VAR vStartDate =
                         EOMONTH ( vToday, -2 ) + 1
                VAR vResult =
                         CALCULATE(
                                     COUNT(vSales[ItemSold]),
                                                    DATESBETWEEN('Calendar'[Date].[Date], vStartDate, vEndDate)
)
Return
       vResult
 
In our database, each item sold makes its own row with corresponding information. If a person buys 4 items, then 4 rows are created in the database.
 
As I have been researching this issue and troubleshooting, I keep thinking that there is something going on with my date table or the references to the date table. When I took a basic count calculation of a table and put the count into a card and then made another card with a TotalYTD measure in it, the numbers are identical even though I have 4-5 years worth of data. There is no way they should be similar numbers. Can my date table be causing this issue? All relationships to tables are active.
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi dbrandone ,

     

    Please check the relationship between your calendar table and vsales table. It should be one to many active relationship.

     

    You can also use the following measure:

     

    Items sold Last Month = 
                VAR vToday =
                         TODAY ()
                VAR vEndDate =
                         EOMONTH ( vToday, -1 )
              
                VAR vResult =
                         CALCULATE(
                                     COUNT(vSales[ItemSold]),
                                                    DATESINPERIOD('Calendar'[Date], vStartDate, 1,MONTH)
)
Return
       vResult

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

5 Replies

    • dbrandone's avatar
      dbrandone
      Helper IV
      5 years ago

      Hi mwegener ,

       

      Yeah, I noticed that. I tried both 'Calendar[Date]' and 'Calendar[Date].[Date]' and both are having issues. I was trying anything I could at that point. I think I tried the .[Date] at the end since intellisense was offering it. I checked and it is marked as the date table.