topic Filter to start showing data on a certain day of the month in DAX Commands and Tips

Filter to start showing data on a certain day of the month

NickDSL — Wed, 27 Sep 2023 21:14:34 GMT

Hi I have the following Calendar table:

My goal here is to have a 1 or 0 filter in a DAX column to show when the "start of month" is for reporting purposes (numbers don't become official until a certain day of the month).

For example:

Today is 9/27/2023 it should return a 0 TODAY. All dates in current month should return a 0 until the 7th of the following month is hit. So in this case when the date hits 10/7/2023 all dates in September should flip to a 1 while keeping 10/1-10/7 a 0. This should loop for all future months. All previous dates should return a 1. In this case 8/1/2023-8/31/2023 should return a 1 TODAY and when the next month hits.

Re: Filter to start showing data on a certain day of the month

nirali_arora — Thu, 28 Sep 2023 03:10:41 GMT

You can try the following measure

ReportingWindowFlag =

VAR CurrentDate = TODAY()

VAR StartOfMonth = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)

VAR EndOfReportingWindow = EDATE(StartOfMonth, 1) + 7

RETURN

IF(StartOfMonth <= CurrentDate && CurrentDate <= EndOfReportingWindow, 1, 0)

Re: Filter to start showing data on a certain day of the month

NickDSL — Thu, 28 Sep 2023 10:37:24 GMT

Hi nirali_arora. This solution does not work. It returns a 1 for all date values even today which should be a 0. I am looking to make a "Helper Column" not a measure. This solution does not work when implemented as either.