Forum Discussion
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.
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.
4 Replies
- bhanu_gautam
Super User
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.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 🙂
v-kpoloju-msftCommunity Support
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!