Want to see current month data

[lavankumar1989a]

Hi All,

Hope all are doing great and safe!

i am looking for a solution for one of my user request. That is, in a report page have 3 slicers like Month, product, country and pie chart, donut, map and bar chart.

When user opens his/her report they want to see current months data by default and later if they want the can go for perious months data.

Lets we have data for below months,

Jan'2021

Feb'2021

Mar'2021

Apr'2021

May'2021

user has to view data initially May data and later they can filter perious months. So kindly assist me to get the query solve.  Kindly share if any sample pbix file to look and create report accordingly.

Thanks for your kind support.

Thanks,

Lavan

[v-xiaotang · Accepted solution]

Hi @lavankumar1989a 

you mentioned that you have 2-3 years data, so I create a sample file with Date between 2019,1 – 2021,5, and you can take it for reference.

-

Create a Calendar Table, then drag the column Date in slicer

Then, create the measure and put it in the visual filter:

Measure = 
var _slicerMonth=IF(ISBLANK(SELECTEDVALUE('Calendar Table'[Date])),TODAY(),SELECTEDVALUE('Calendar Table'[Date]))
var _startdate=DATE(YEAR(_slicerMonth),MONTH(_slicerMonth),1)
var _enddate=IF(MONTH(_slicerMonth)=12, DATE(YEAR(_slicerMonth)+1,1,1),DATE(YEAR(_slicerMonth),MONTH(_slicerMonth)+1,1))
return IF(SELECTEDVALUE('Table'[Date])>=_startdate && SELECTEDVALUE('Table'[Date])<_enddate,1,0)

Result:

See sample file attached bellow.

Best Regards,

Community Support Team _ Tang

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

[Tahreem24]

@lavankumar1989a ,
I have one workaround for you. Do the below steps:

Step 1: Create one calculated column like below:

Month_ =  'TableName'[Your Month Column].[MonthNo]

Step 2: Then create another and final calculated column like below:

Default Month Column = IF('TableName'[Month_]=MONTH(TODAY()) && 'TableName'[Your Month Column].[Year]=YEAR(TODAY()), "Current Month", "Not Current Month")

Step 3: Take one slicer and first put this above column created in Step 2 (Default Month Column) and add also Your Month Column. 

See the below Screen shot:

[Anonymous]

use a rank function and create a calculated column, you can then sort your month based on the column.

Not sure what you meant by query, please elaborate.

@lavankumar1989a 

[lavankumar1989a]

@Anonymous ,

Thanks for your reply. query means my requirement.

once again will explain the requirement, i have 5 months data for the year like below

Jan'2021

Feb'2021

Mar'2021

Apr'2021

May'2021

user wants to see latest month(May'2021) data by default and later user can able to filter perious months as per his need. Kindly share the sample .pbix or explain clearly to achive the requirement.

Hope you are saying like if i apply rank for month number? if we have 2 to 3 years data it won't work right as May'2021 and May'202 has month number will be like 5 right.

Thanks,

Lavan

[Anonymous]

Hi @lavankumar1989a 

Please see the below picture, rankx will consider year as well and rank your data accordingly. Now if you are going to use this rankx function on your date column, please create a duplicate date column and try to apply the rankx for that duplicate column of date and then apply sort by column on the top ribbon to the date column original based on the ranked column.This will help you resolve the failed to save error due to circular reference.

Thanks

And don't forget to accept my solution, if it answers your query 🙂😃