How to hide first few days of current month of data until a certain number of days have passed?

[alicek]

Hi all,

I have a date table where each row is a single day, based on the minimum and maximum of another data source (max is always 5 days earlier than today). I put it on the x-axis of my over time line charts. 

I figured out a way to make the chart always show only up to the most recent COMPLETED month. So, for example, all of May will show, but none of June will show until June 30th has passed. 

However, I need a way to make it show any of June only after 15 days of June has passed. So, from June 1 through June 14, show only through May; on June 15 or after, show as much data in June is available (so prior June becomes available).

Can anyone help me with some DAX or M that will get me there?

Here are two formulas that I'm trying to make work. The first one unfortunately never shows any dates from the first 15 days of the current month (it doesn't show after 15 days have passed, it just never shows those dates). I can't figure out how to make the second work with filters.

First option:
Current Month and Day 15 =

IF(

AND(

(FORMAT([Date], "MMMM YYYY")) = (FORMAT(MAX('Date Table'[Date]), "MMMM YYYY")),

DAY(LASTDATE('Date Table'[Date])) < 14

),

"TOO EARLY TO SHOW",

"SHOW")

Second - a calculated column that identifies the month and shows the 15th in the cell of that month.

MonthDay15 = 'Date Table'[Date] - DAY('Date Table'[Date])+15

Thank you!

[alicek · Accepted solution]

I believe a colleague found the right solution. If anyone else stumbles across this question, here is a solution:

Create the following calculated column:

Flag current month if max day is <15 = 

VAR maxday_day = DAY(max('Date Table'[Date]))
VAR latestmonth = MONTH(max('Date Table'[Date]))
VAR latestyear = YEAR(max('Date Table'[Date]))

RETURN
 IF( 
    AND( 
      AND( maxday_day <= 14, 
           'Date Table'[Month]=latestmonth
          ),
      'Date Table'[Year]=latestyear
     ),
 1, 
 0 )

You would then drag this column onto the filter pane for the required visual and make is "Show anything greater than 0"

[v-deddai1-msft]

Hi @alicek ,

Did you want to show date from 1-14 if today is after 15 every month? You can use the following measure:

Current Month and Day 15 =
IF (
    DAY ( TODAY () ) <= 14,
    "TOO EARLY TO SHOW",
    IF (
        MAX ( Date[Date] ) <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 15 )
            && MAX ( Date[Date] ) <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
        "SHOW"
    )
)

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

Best Regards,

Dedmon Dai

[alicek]

Hi Dedmon! I'm not sure this would work because:
1. I think the second && MAX should be => not <=? as in, if it's between 1 and 15 - the way you have it written there is if the latest/max date of the date table is both less than or equal to the 15th and less than or equal to the 1st.

2. The first part of the IF statement would make any date in the date table that had the "day" number as less than 14 always say "too early to show." For example, if today was June 14th, May 14 would also meet the criteria of DAY(TODAY()) <= 14