Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Count events between two dates

Hey there   I'm facing a situation where I must calculate how many occurrences (from the Fact table) took place between two dates (that also comes from my Fact table).     This is my fact ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    This piece of code solved my problem:

     

    COUNTROWS(FILTER(ALL(Data), MAX('Calendar'[Date]) > [EventStart] && MAX('Calendar'[Date]) < [EventEnd]))
mangaus1111's avatar
mangaus1111
Solution Sage
3 years ago

Hi Anonymous ,

 

try this:

 

VAR Open Events =
SELECTCOLUMNS (
    GENERATE (
        Fact Table,
        DATESBETWEEN (
            'Date'[Date],
            Fact Table[EventStart],
            Fact Table[EventEnd] - 1
        )
    ),
    "TransactionID", Fact Table[TransactionID],
    "Date", 'Date'[Date]
)
 
RETURN
COUNTROWS(Open Events)
 
Please hit the thumbs up & mark it as a solution if it helps you. Thanks.
  • mangaus1111's avatar
    mangaus1111
    Solution Sage
    3 years ago

    sorry, instead of 

    RETURN
    COUNTROWS(Open Events)
     
    you must use
    DISTINCTCOUNT(Open Events[TransactionID])
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Unfortunely it didn't work. Using countrows I got a huge number and when I tried using Distinctcount, this error pops up:

    “The DISTINCTCOUNT function only accepts a column reference as an argument.”

     

    Thanks for your help, friend.