topic Dimension column based on page date slicer value - possible? in DAX Commands and Tips

Dimension column based on page date slicer value - possible?

Anonymous — Thu, 15 May 2025 06:13:09 GMT

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.

Re: Dimension column based on page date slicer value - possible?

bhanu_gautam — Thu, 15 May 2025 08:19:09 GMT

Anonymous 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.

Re: Dimension column based on page date slicer value - possible?

v-kpoloju-msft — Thu, 15 May 2025 11:38:13 GMT

Hi Anonymous,

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!

Re: Dimension column based on page date slicer value - possible?

Anonymous — Fri, 16 May 2025 00:31:37 GMT

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 🙂

Re: Dimension column based on page date slicer value - possible?

Anonymous — Thu, 15 May 2025 23:31:32 GMT

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