Forum Discussion

CoffeeTime's avatar
CoffeeTime
Frequent Visitor
6 years ago

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_Deckler's avatar
    Greg_Deckler
    Community 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.

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion

        Yes, you will need to make everything you described done in measures.