Power Query - Logical Problem

[dan1234]

Hi everyone,

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

Any thought in getting this solved is appreciated. I tried to attach the pbix file as well but I couldn't do that.

Current state

I have a data table like the one below:

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.

Can this be achieved?

[lbendlin · Accepted solution]

Keep in mind that slicers need to be fed by columns. They cannot be fed by measures.

So either create a calculated table in DAX or a separate query in Power Query that lists the dates as needed.

As you may have realized already this only works in import mode with frequent (daily) dataset refreshes.

[lbendlin]

Power BI has no memory, and no concept of global variables. You need to do that snapshotting  in the upstream system.

[dan1234]

Thanks for checking this. I can do snapshotting upstream; it is the date selection that I couldn't work out. Sorry if that is not clear. I am stuck on how to get the prior month's and before the last month's data only to be seen in the one report view based on the 15th of every month. If we are before 15th of November then report should show September 2022 as the report date otherwise it is October 2022

[lbendlin · Accepted solution]

Keep in mind that slicers need to be fed by columns. They cannot be fed by measures.

So either create a calculated table in DAX or a separate query in Power Query that lists the dates as needed.

As you may have realized already this only works in import mode with frequent (daily) dataset refreshes.

[dan1234 · Accepted solution]

Thank you, I will try that.