Forum Discussion

PPStar's avatar
PPStar
Helper V
3 years ago
Solved

Filter table to show data for current month

Hi,    i have a table (Called Records) which looks like below   Number | Created On  12432    | 12/4/2021 332535  | 15/05/2022 334525 | 01/01/2023 435315 | 14/01/2023 335325 | 2/5/2021   T...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  PPStar ,

     

    Here are the steps you can follow：

    1. Create calculated table.

     

    records =
var _table=
SUMMARIZE(
    'Table','Table'[Create On],'Table'[Number],"Year",YEAR('Table'[Create On]),"Month",MONTH('Table'[Create On]))
return
FILTER(
    _table,[Year]=YEAR(TODAY())&&[Month]=MONTH(TODAY()))

     

    2. Result:

    Does it meet your expected results

     

    Best Regards,

    Liu Yang

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

MAwwad's avatar
MAwwad
Solution Sage
3 years ago

 

You can use the FILTER() and SUMMARIZE() functions in DAX to filter your data based on the current year and month. Here's an example of a DAX formula that should do this:

 

Copy code
FILTER( SUMMARIZE( FILTER(Records, 'Date'[Year] = YEAR(NOW()) && 'Date'[MonthNumber] = MONTH(NOW())), 'Date'[Number], "NumberCount", COUNTX(CURRENTGROUP(), 'Date'[Number]) ), [NumberCount] > 0 )
 

This formula first filters your "Records" table based on the current year and month using the year and month columns in your date table. Then it summarizes the filtered data by counting the number of records for each "Number" value. Finally, it filters the summarized data to only include values with a count greater than zero.

  • PPStar's avatar
    PPStar
    Helper V
    3 years ago

    This doesnt work. 

    FILTER(SUMMARIZE(FILTER(Records,  - from here, i am unable to select my date table ?