Last Month Calculations not functioning

[dbrandone]

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.

[mwegener]

Hi @dbrandone ,

the 'Calendar'[Date].[Date] looks wrong, it should be 'Calendar'[Date].

Please check if you have marked your Calendar table as a date table.

https://www.sqlbi.com/articles/mark-as-date-table/

[dbrandone]

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. 

[v-deddai1-msft · Accepted solution]

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

[mwegener]

Hi @dbrandone ,

it works in my sample file.

(see attached file)

[mwegener]

Hi @dbrandone ,

can you provide a sample file?