How to Pull Data from Prior Month Triggered with Bimonthly flag

[RogueCheddar209]

All,

I have been able to add a column to my Date table indicating whether the days fall between the 1st-14th and the 15th to EOM. What I need help with is creating an additional column in the Date table that is Yes for the previous bi-monthly period so that I can dynamically filter my report.

Example: Say that Today is 2/4/2000. In the sample table below, I already have the [BiMonth] and [IsCurrentPeriod] columns worked out. I need to create the [IsReportingPeriod] column so that the previous bimonthly period is flagged Yes. It seems like it should be simple, but I just can't work out the logic. Thank you in advance for your help.

Date	Month	Day	BiMonth	Is CurrentPeriod	IsReportingPeriod
1/13/2000	1	13	A	No	No
1/14/2000	1	14	A	No	No
1/15/2000	1	15	B	No	Yes
1/16/2000	1	16	B	No	Yes
1/17/2000	1	17	B	No	Yes
1/18/2000	1	18	B	No	Yes
1/19/2000	1	19	B	No	Yes
1/20/2000	1	20	B	No	Yes
1/21/2000	1	21	B	No	Yes
1/22/2000	1	22	B	No	Yes
1/23/2000	1	23	B	No	Yes
1/24/2000	1	24	B	No	Yes
1/25/2000	1	25	B	No	Yes
1/26/2000	1	26	B	No	Yes
1/27/2000	1	27	B	No	Yes
1/28/2000	1	28	B	No	Yes
1/29/2000	1	29	B	No	Yes
1/30/2000	1	30	B	No	Yes
1/31/2000	1	31	B	No	Yes
2/1/2000	2	1	A	Yes	No
2/2/2000	2	2	A	Yes	No
2/3/2000	2	3	A	Yes	No
2/4/2000	2	4	A	Yes	No

[Anonymous · Accepted solution]

Hi @RogueCheddar209 ，

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a date dimension table and use the date field in date dimension as slicer selection

Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

2. Create a measure as below to get the flag

IsReportingPeriod = 
VAR _seldate =  SELECTEDVALUE ( 'Date'[Date] )
VAR _selday =   DAY ( _seldate )
VAR _selmonth =  MONTH ( _seldate )
VAR _selprieomonth =  EOMONTH ( _seldate, -1 )
VAR _tday =  SELECTEDVALUE ( 'Table'[Day] )
VAR _tmonth =   SELECTEDVALUE ( 'Table'[Month] )
VAR _premonth =   IF ( _selmonth = 1, 12, _selmonth - 1 )
RETURN
    IF (
        _selday >= 1
            && _selday <= 14,
        IF (
            _tmonth = _premonth
                && _tday >= 15
                && _tday <= DAY ( _selprieomonth ),
            "Yes",
            "No"
        ),
        IF ( _tmonth = _selmonth && _tday >= 1 && _tday <= 14, "Yes", "No" )
    )

Best Regards

[RogueCheddar209]

This was so close to what I needed and your code pointed me in the right direction. Here is my tweaked code that ended up working for me.

IsReportingPeriod =

VAR _caldate = 'Date'[Date]

VAR _calday = DAY ( _caldate )

VAR _calmonth = MONTH ( _caldate )

VAR _calprieomonth = EOMONTH ( _caldate, -1 )

VAR _tday = TODAY()

VAR _tmonth = MONTH ( _tday )

VAR _premonth = IF ( _tmonth = 1, 12, _tmonth - 1 )

RETURN

IF(

DAY( _tday ) >= 1

&& DAY( _tday ) <= 14

&& YEAR( _tday ) = YEAR( _caldate ),

IF (

_calmonth = _premonth

&& _calday >= 15

&& _calday <= DAY ( _calprieomonth ),

"Yes",

IF(

DAY( _tday ) >=15

&& DAY( _tday ) <= _calprieomonth

&& YEAR( _tday ) = YEAR( _caldate ),

"Yes",

"No"

)

),

"No"

)

[RogueCheddar209]

Thank you for the reply but this does not seem to get the desired result. I need to filter my date table to only the half-month prior i.e October 1st, we want to see data from Sept 15th-30th. On October 15th, we want to see data from October 1st - 14th. Also, I need to make sure the axis dates are offset as well. I have searched for a bi-monthly period calendar but there doesn't seem to be anything on the topic.

[amitchandak]

@RogueCheddar209 , based on what i got, Can you create a period start date for you each bi month. then you can create a period rank ( do this in a date table)

new column

Period Rank = RANKX(all(Period),Period[period start date],,ASC,Dense)

example measures
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))