Forum Discussion
Using a measure to store a variable and using it on a calculated column
I would like to compare weeks of the current month with and average of previous month.
To do this, I've created 2 measures, one suming the month we are in and another calculating the previous months average. The visual is shown below:
Week visual comparison:
To compare weeks that doesn't have the same date I had to create 2 calculated columns like Semana (Mes), Semana (Intervalo) and "Semana":
"Semana (Mes)" returns month week number and "Semana (Intervalo)" returns the week dates interval.
Ok, then I created a calculated column "Semana" that replicates the "Semana (Intervalo) to all calendar table based on the actual month. The DAX is shown below:
I works fine, but now, I would like to select the comparison month in a slicer. To do that I created a simplified copy of the calendar table:
I inserted a slicer to select the desired month and saved it as a measure. I then tried referencing it on the calculated column from the original calendar table:
I get a blank column. I also tried bringing the max date from the sliced simplified calendar table and filter the original table, but I always get the table's max value, as if there was no slicer.
Sorry for the long post. Can someone help me out? Thnx
4 Replies
- Greg_DecklerCommunity Champion
It appears as if you are trying to have a calculated column calculate dynamically based upon user input in a slicer. You cannot do that. Calculated columns only get at the time data is loaded.
- CoffeeTimeFrequent Visitor
Any workaround suggestion?
- Greg_DecklerCommunity Champion
Yes, you will need to make everything you described done in measures.