Forum Discussion

Neqom's avatar
Neqom
Frequent Visitor
2 years ago
Solved

Count Based on Date in the same Table

I have a Table with the below structure: ID - Date 1 - 1/Jan/2020 1 - 2/Jan/2020 2 - 1/Jan/2020 3 - 1/Jan/2020 4 - 5/Jan/2020 ... Where ID appears multiple times. I need a measure in a Ta...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Neqom ,

    Here's my modified data, otherwise unchanged.

     

    Just select Show item with no data in the ID field and now, IDs with no counts in the last 30 days show blank.

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Neqom ,

 

Thanks for the reply from Awm .

 

First, create a measure to store your definition of today=2020/1/10:

_today = DATE(2020,1,10)

 

Then, create a measure to count the number of rows:

Count = CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table'.
            'Table'[Date] >= [_today] - 30 && 'Table'[Date] < [_today]
        )
    )

 

The page visualization is shown below:

 

If you want to calculate the last 30 days of data for today (2024/05/07), you can replace _today with Today().

Count = CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[Date] >= TODAY() - 30 && 'Table'[Date] < TODAY()
        )
    )

 

The pbix file is attached.

 

If you have any other questions please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Neqom's avatar
Neqom
Frequent Visitor
2 years ago

Hello!
Thanks for your help.
Iy works. however it shows blank instead of "0" for ID where there is nothing in the last 30 days
Sorry for no specify that at the beginning.


  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Neqom ,

    Here's my modified data, otherwise unchanged.

     

    Just select Show item with no data in the ID field and now, IDs with no counts in the last 30 days show blank.

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!