Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
wokka
Helper IV
Helper IV

Dimension column based on page date slicer value - possible?

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.

 

1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

4 REPLIES 4
v-kpoloju-msft
Community Support
Community Support

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!

Hi

 

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

bhanu_gautam
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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  🙂

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.