Forum Discussion
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
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
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
5 Replies
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 codeFILTER( 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.
This doesnt work.
FILTER(SUMMARIZE(FILTER(Records, - from here, i am unable to select my date table ?
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
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?
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
