Power Query/Power BI - Logical Problem

Power Query
1

Hi everyone,

I am posting a logical problem, hoping to get expert guidance.

Any thought in getting this solved is appreciated. Attaching the PBI file
Current state

I have a data table like the one below:

Initiative Name Refresh Date
aaa 30/09/2022
bbb 30/09/2022
cccc 30/09/2022
dddd 30/09/2022
eeee 30/09/2022
ffff 30/09/2022
ggg 30/09/2022
hhh 30/09/2022
iii 30/09/2022
jjj 30/09/2022
kkk 30/09/2022
llll 30/09/2022
mmm 30/09/2022
nnn 30/09/2022
ooo 30/09/2022
pppp 30/09/2022
qqqq 30/09/2022
rrrr 30/09/2022
sss 30/09/2022

Several Initiatives get recorded daily from a Sharepoint list which describes the initiative’s name and the Refresh date. The refresh date will be the same for all the initiatives at a point in time. It is the current date. This gets stored in the Data Table 1 View in Pbix.

I need to take a month-end snapshot of this view at the month’s end, and it gets appended to the main data table. - Master Table

Initiative Name Refresh Date
aaa 30/09/2022
bbb 30/09/2022
cccc 30/09/2022
dddd 30/09/2022
eeee 30/09/2022
ffff 30/09/2022
ggg 30/09/2022
hhh 30/09/2022
iii 30/09/2022
jjj 30/09/2022
kkk 30/09/2022
llll 30/09/2022
mmm 30/09/2022
nnn 30/09/2022
ooo 30/09/2022
pppp 30/09/2022
qqqq 30/09/2022
rrrr 30/09/2022
sss 30/09/2022
aaa 31/10/2022
bbb 31/10/2022
cccc 31/10/2022
dddd 31/10/2022
eeee 31/10/2022
ffff 31/10/2022
ggg 31/10/2022
hhh 31/10/2022
iii 31/10/2022
jjj 31/10/2022
kkk 31/10/2022
llll 31/10/2022
mmm 31/10/2022
nnn 31/10/2022
ooo 31/10/2022
pppp 31/10/2022
qqqq 31/10/2022
rrrr 31/10/2022
sss 31/10/2022
aaa 1/11/2022
bbb 1/11/2022
cccc 1/11/2022
dddd 1/11/2022
eeee 1/11/2022
ffff 1/11/2022
ggg 1/11/2022
hhh 1/11/2022
iii 1/11/2022
jjj 1/11/2022
kkk 1/11/2022
llll 1/11/2022
mmm 1/11/2022
nnn 1/11/2022
ooo 1/11/2022
pppp 1/11/2022
qqqq 1/11/2022
rrrr 1/11/2022
sss 1/11/2022

Proposed state

The ask is to create a slicer that helps to show only one month at a particular time, depending on the day. The data gets filtered based on that month.

i.e. if today is before the 15th of every month ( working day), the slicer should show prior to the prior month and if it is after 15th, it is the prior month. Ie on 01/11/2022, the Report refresh date date should show September 2022 initiative data, and if the day is after 15/11/2022, the report date slicer should be October 2022.

I have tried to create a column in the power query that achieve this but couldn’t workout prior month and prior to prior month based on the 15th.

image
image819Ă—445 41.4 KB

Slicer_Creation.pbix (73.0 KB)

Can this be achieved?

2

Hi @nadeena_mini,

Thanks for supplying a file with data :+1:

Can you see if something like this works for you.

image
image936Ă—472 34.3 KB

Here’s the Master Table query:
.

let
    cDate = /* #date(2022,11,14), */ Date.From( DateTime.FixedLocalNow() ),
    pDate = Date.AddMonths( Date.StartOfMonth( cDate ), -2 ),
    Source = Table.Combine({#"Data Table 1", MonthEnd_Snapshot_Oct, MonthEnd_Snapshot_Sep}),
    InsertFilter = Table.AddColumn( Source, "ShowPeriod", each
        if Date.Day( cDate ) >= 15
        then Date.IsInPreviousMonth( [Refresh date] )
        else pDate = Date.StartOfMonth([Refresh date]),
    type logical )
in
    InsertFilter

I hope this is helpful

2 Likes
3

Hi Melissa,

Thank you so much, this worked! I couldn’t workout the Cdate logic, thank you.

4

Glad I could help @nadeena_mini, please don’t forget to mark the post that answered your question as Solution via … (the 3 dots in the bottom right corner of that reply).

Thank you!!