Dimension column based on page date slicer value - possible?

[wokka]

I need to find out whether this is possible - we have a dimension table that has a column [First of Month] - which has data running from 01/01/2022,  01/02/2022,.....  up  to 01/05/2025. 

If we build a normal graph, we can choosr the first of a month and this will fill in the data as needed. This is existing functionality and works well. All good.

But - I need to builld two powerbi graphs - one that uses the chosen [First of month] value via slicer on the page  e.g.  01/10/2024 and a second graph that uses the preceding [First of month] e.g. 01/09/2024. The two graphs need to sit side by side on the page and as I alter the first of of month data slicer, both graphs need to automatically change.

What I want to do is ( i think ) is in effect create a synthetic column on the fly for 01/09/2024. The issue is that we dont have column in the existing calendar table for the [Preceding first of month] ( which would be ideal ) so my question is DAX experts , can we do it, and if so, how could we do it please?

I have trialled setting up my second graph ( for the preceeding month -  01/09/2024 ) that is de-coupled from the page date slicer value , but we currently have to manually choose the first of month date, which we would love to avoid. 

Thank you in advance.

[v-kpoloju-msft]

Hi @wokka,

May i know has your issue been resolved? If the response provided by the community member @bhanu_gautam, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

[wokka]

Hi

Appreciate you want things wrapped up quickly, will try my best.

[bhanu_gautam · Accepted solution]

@wokka You can create a measure that calculates the preceding month based on the selected [First of Month] value from the slicer. 

Create a measure for the selected [First of Month]:

SelectedFirstOfMonth = SELECTEDVALUE('Calendar'[First of Month])

Create a measure for the preceding [First of Month]:

PrecedingFirstOfMonth = EOMONTH([SelectedFirstOfMonth], -1) + 1

Create a measure to filter the data for the preceding month:

DAX
PrecedingMonthData =
CALCULATE(
SUM('YourTable'[YourDataColumn]),
'Calendar'[First of Month] = [PrecedingFirstOfMonth]
)

Use the SelectedFirstOfMonth measure to filter the data for the selected month:

DAX
SelectedMonthData =
CALCULATE(
SUM('YourTable'[YourDataColumn]),
'Calendar'[First of Month] = [SelectedFirstOfMonth]
)

For the first graph, use the SelectedMonthData measure.
For the second graph, use the PrecedingMonthData measure.

[wokka]

Hi,

The first 2 pieces of DAX code I understand OK.

So the code appears to sum up the column [YourDataColumn] in the table 'YourTable' , for the lines in the table where 'Calendar'[FirstofMonth] = <some date>. Is that correct?

Also, SUM is normally for adding up numerical values to give a total, I am curious to know what this does with dates please?

Could you please explain which table this is  --->  'YourTable'[YourDataColumn]

Is this the 'Calendar'  table, or is it table thats displaying the output data on the powerbi desktop, or is it maybe something else ?

PrecedingMonthData =
CALCULATE(
SUM('YourTable'[YourDataColumn]),
'Calendar'[First of Month] = [PrecedingFirstOfMonth]
)

I have a SQL background and DAX seems to be really quite different to TSQL.  

Thanks in advance, just trying to understand  🙂