Filter table to show data for current month

[PPStar]

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

The table has around 55,0000 rows of data. 

I need to be able to write a DAX which shows me data for the current year and month only. 

The Records Table has a 1:M relationship with a date table. The Date Table has info as below

I have been trying to use the Filter(Summarize) Function, but to no avail. 

What i need is every month, this DAX query should run (via power automate) and show data for Jan, Feb, March, so i can then archive the data. 

Can someone please show me how i can write some DAX to only show Records data for the current month, so it can automatically just run?

Thanks

[v-yangliu-msft · Accepted solution]

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

[v-yangliu-msft]

Hi  @PPStar ,

I created some data:

Date:

Here are the steps you can follow：

Create calculated table.

Monthly data:

Every Month =
var _table=SUMMARIZE('Date','Date'[Year],'Date'[Month])
return
ADDCOLUMNS(
    _table,"Value",
    SUMX(FILTER(ALL('Table'),
    YEAR('Table'[Create On]) =[Year] && MONTH('Table'[Create On]) =[Month]),[Number]))

Current month's data:

Current Month =
var _table=
SUMMARIZE('Date','Date'[Year],'Date'[Month])
var _table2=
FILTER(
    _table,
    [Year] = YEAR(TODAY()) && [Month] =MONTH(TODAY()))
return
ADDCOLUMNS(
    _table2,"Value",
    SUMX(FILTER(ALL('Table'),
    YEAR('Table'[Create On]) =[Year] && MONTH('Table'[Create On]) =[Month]),[Number]))

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

[PPStar]

Thanks for this. But would this return me the records for that month?. I need to see all the records for that particular month, not the value of records?

[MAwwad]

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]

This doesnt work. 

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