How to build "Curren tPeriod" flag in calendar table based on the main Date column

[Mk60]

I have Calendar table with these columns, which are build based on the date column "POSTING_DATE" that is in my main "Query1Loan" which updates every month. When I use that attribute/flag POSTING_DATE as filter with any visual that is using calculated metrics based on my Calendar table columns, utilizing Time intelligence form POwer BI, I can't make it work as my filter. Apparently does not work for some reason, and I get that Query1Loan from other sources, I am not creating it.  Wonder if I can build my own Current Period in my Calendar table using dax, and how I could write that dax calculation. Thank you so much for any suggestion.

[v-veshwara-msft]

Hi @Mk60 ,

Thanks for reaching out to Microsoft Fabric Community and sharing the detailed context.

Based on your description, where the dataset is refreshed monthly around the 7th business day with previous month-end performance, Here’s an updated version you can add as a calculated column in your Calendar table:

IsCurrentPeriod = 
VAR MaxPostingDate =
    CALCULATE (
        MAX ( Query1Loan[POSTING_DATE] ),
        REMOVEFILTERS ( Query1Loan )
    )
VAR MaxMonthEnd =
    EOMONTH ( MaxPostingDate, 0 )
RETURN
    IF (
        EOMONTH ( 'Calendar'[Date], 0 ) = MaxMonthEnd,
        TRUE(),
        FALSE()
    )

To support visuals showing only the last 5 quarters, you can also add the following:

IsInLast5Quarters = 
VAR MaxPostingDate =
    CALCULATE (
        MAX ( Query1Loan[POSTING_DATE] ),
        REMOVEFILTERS ( Query1Loan )
    )

VAR MaxYear = YEAR(MaxPostingDate)
VAR MaxMonth = MONTH(MaxPostingDate)

// Determine quarter start of MaxPostingDate
VAR MaxQuarterStartMonth = SWITCH(
    TRUE(),
    MaxMonth IN {1, 2, 3}, 1,
    MaxMonth IN {4, 5, 6}, 4,
    MaxMonth IN {7, 8, 9}, 7,
    10
)

VAR MaxQuarterStartDate = DATE(MaxYear, MaxQuarterStartMonth, 1)
VAR ThresholdDate = EDATE(MaxQuarterStartDate, -15)  // Go back 5 quarters

RETURN
    IF (
        'Calendar'[Date] >= ThresholdDate
        && 'Calendar'[Date] <= MaxPostingDate,
        TRUE(),
        FALSE()
    )

You can now use these flags in slicers or filters to restrict visuals to the latest available month or 5-quarter window.

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.

Thank you.

Please find the attached .pbix file for reference.

[Mk60]

Hi @v-veshwara-msft 

Thanks so much once again! Trully apprecite your willingness to help me here. I tried your "IsInLast5Quarters" filter and it is not providing me what I was looking for, and I am sure the reason is that I need to provide you one more detail, and you'll probably be able to modify this filter to capture ONLY values just for the last "Quarterly month-end", (Jun, Sep, Dec, Mar, Jun,). Although users want to call them Q1 2024, Q2, 2024, etc, but the metrics need to be as of Mar2024, Jun2024, etc). So, I'll include few more pictures here, and show you samples for:

Pictuer#1. Actual monthly table with all values/numbers which is the base to create 5 recent quarters table I need

Picture#2. Table with actual quarter-end results that I need to capture, 

Picture#3. Table where I used your "IsInLast5Quarters" filter, (where Power BI Time intelligence basically adds 3 months of each quarter and divide by 3 to give me tecnically accurate values, but not only last month of each quarter that I need). I.e. Q1.2025 accurate value is 37.90%, (picture#2), and using your filter is giving me 34.01%, (37.90%+34.99%+29.13%)=102.02/3=34.01%)

FYI only-for the Q2 2025 in my case, report will be showing May2025 data for now, and once Jun data is loaded, early next month, that number will still correctly show Q2 2025, but data/values will be accurately reflecting June2025, (final for Q2 2025)

 Sorry for the long explanation. Hope this will help little more to have a revised dax that will be capturing only last month of each quarter values, and still keep only most recent 5 quarters, (which is 5 quarterly month-ends) in the table visual. Much appriciated!

[v-veshwara-msft · Accepted solution]

Hi @Mk60 ,
Thank you for the additional context and screenshots. Based on your clarification, here’s the updated DAX for Calendar table that flags only the last month of each quarter (i.e Mar, Jun, Sep, Dec, Jun), limited to the 5 most recent completed quarter-ends based on the latest available POSTING_DATE:

IsInLast5Quarters = 
VAR MaxPostingDate =
    CALCULATE (
        MAX ( Query1Loan[POSTING_DATE] ),
        REMOVEFILTERS ( Query1Loan )
    )

VAR MaxMonth = MONTH ( MaxPostingDate )
VAR MaxQuarterEndMonth =
    SWITCH (
        TRUE (),
        MaxMonth IN { 1, 2, 3 }, 3,
        MaxMonth IN { 4, 5, 6 }, 6,
        MaxMonth IN { 7, 8, 9 }, 9,
        12
    )

VAR MaxQuarterEndDate = DATE ( YEAR ( MaxPostingDate ), MaxQuarterEndMonth, 1 )

VAR QuarterEndList =
    ADDCOLUMNS (
        GENERATESERIES ( 0, 4, 1 ),
        "QuarterEnd",
            EOMONTH ( MaxQuarterEndDate, - [Value] * 3 )
    )

VAR ValidQuarterEnds =
    SELECTCOLUMNS ( QuarterEndList, "QuarterEnd", [QuarterEnd] )

RETURN
    IF (
        EOMONTH ( 'Calendar'[Date], 0 ) IN ValidQuarterEnds,
        TRUE (),
        FALSE ()
    )

You can now use this column in your visual filter pane (e.g. IsInLast5Quarters= TRUE()) to restrict the visuals to just those months.

Hope this helps.

If this doesn’t fully meet your needs, please consider sharing a sample PBIX file or simplified sample data so we can guide you more precisely.

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

Thank you.

Please find the attached .pbix file for reference.

[Mk60]

Hi @v-veshwara-msft 

Once again, I just wanted to express my sincere gratitude for your time and passion to provide the best solution for my perhaps little weired request. Your revised dax does exactly what I was looking for, and I hope other users find it useful as well. 

If you don't mind, I'll add one more "bonus" question, just for my own practice; Here's the final table with your revised dax, looks perfect, and I was only wondering if there is an easy twist to your dax syntax to allow for the "Not full quarters periods", in this case Q2 2025 to show most recent month values until the full quarter data is loaded next month to populate/finilize last quarter? Again, just for my own practice. Thanks so much, once again for all the education and your patience 🙂

[danextian]

If by Current Period you mean the current month, you can create a calculated column to compare Month Year with today's Month Year

Is Current Period =
FORMAT ( TODAY (), "mmmyy" ) = 'Calendar'[Month Year]

[pankajnamekar25]

Hello @Mk60 

Use the dax to add calculated column in Calendar table

IsCurrentPeriod =

VAR MaxPostingDate = CALCULATE(

    MAX(Query1Loan[POSTING_DATE]),

    REMOVEFILTERS(Query1Loan)

)

RETURN

IF('Calendar'[Date] = MaxPostingDate, TRUE(), FALSE())

RETURN

IF('Calendar'[Date] = MaxPostingDate, TRUE(), FALSE())Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Mk60]

Hi @pankajnamekar25 

Thanks so much for your input. Your dax tecnically works fine, but when I select  "True" in my filter it shows Date as 6/30/2025, which is tecnically correct since we're in June now. However, I did not provide sufficient information, so here's additional context, which should help. I refersh my data only once a month, appending account level monthly detail for the previous month-end performance to my current history in the model Query1Loan. New month-end permormance data is refreshed at about 7th business day in the current month, with the previous month-end, so right now (June), my Current_Period data is May2025, in July it would be June month-end added at 7th bus dat, and so forth. I know Calendar table has already full year with all months for 2025, so my challange is to have some kind of "flag" or filter to be able to use my historical data from Jan2023, (which is my first available month), to the most recent month-end that I have available in my data set. So hopefuly that migh help you to customize the dax you provided?

In addition, If I may add, I would need similar "flag" to help me to filter only 5 most recent Quarters in my visuals, and currently I can't eliminate other quarters showing up through the end of year, which obviousely I do not have any data for yet.

Here's few more pic to show my Date attribute in the main query, and how I connected date filed to my Calendar table. Thanks much for your time, and applogogize for not providing more details first time.