Retain current value of column

[Anonymous]

Is there a DAX function or some way to retain original value of a calculated column?

Eg: IF I have a column say "Column". Formula for Column is :

Column = IF (
FORMAT ( WEEKDAY ( TODAY () ), "dddd" ) = "Tuesday",
[Column1],
CURRENT VALUE
)

Column values will only be updated on Tuesday, on other days it should show the value from last Tuesday.

[v-xuding-msft]

Hi @Anonymous ,

I think you want to calculate the data on Tuesday. If  today is Tuesday, then display the data today. If not , then display the data on the last Tuesday.  

If I understand correctly, you can try the following formula. Otherwise , please post the result you want , then we can help you quickly.

Column =

VAR last =

CALCULATE (

MAX ( 'Sales'[Date] ),

FILTER ( ALL(Sales), WEEKDAY ( 'Sales'[Date], 2 ) = 2 && 'Sales'[Date] < TODAY() )

)

RETURN

IF (

WEEKDAY(TODAY(),2)=2,

CALCULATE ( SUM ( Sales[Sales] ), FILTER ( Sales, 'Sales'[Date] = TODAY() ) ),

CALCULATE ( SUM ( Sales[Sales] ), FILTER ( Sales, 'Sales'[Date] = last ) )

)

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @ v-xuding-msft,

Thank for your reply. But my problem is little complicated that that. May be I should have described it in detail earlier.

Let me put it in simple way:

I have a master table 'Table 1' and I created antother table 'Table 2' using summarize on 'Table 1'. In 'Table 2' I have added some additional columns. 

I want to update columns in Table 2. Third column 'Total Reviewed' will change on daily basis but second column will only update on Tuesday.

On wednasday if user have reviewed some of the tasks last 2 columns will change to show the progress but not the second column.

Wednesday View

I am using following formula that gives me total reviewed on current date and time:

CALCULATE (
            COUNTROWS('Table1'),
            FILTER('Table 1','Table 1'[User]=[User]),
            FILTER('Table 1','Table 1'[Reviewed]="Y")
)

But, when I put this formula in IF condition, it gives me correct result on Tuesdays. But, on other days it leaves the second column blank.

IF (
    FORMAT ( WEEKDAY ( TODAY () ), "dddd" ) = "Tuesday",
    CALCULATE ( 
          COUNTROWS('Table1'),
          FILTER('Table 1','Table 1'[User]=[User]),
          FILTER('Table 1','Table 1'[Reviewed]="Y") 
     )
)

Note that I havent specified any value for false condition.

I tried using 'SWITCH' instead of IF wich also returned same result.

May be there isnt a DAX way to achive what I want to do here. Need to figure out another way to do this.

[SNawalKishore1]

Hello @Anonymous,

Please find attached PBIX (URL) and let me know is this suffice.

---

Cheers,

Nawal
+91-9866755720 | Power BI Consultant

https://www.linkedin.com/in/snawalkishore/

[Anonymous · Accepted solution]

Hi @SNawalKishore1 ,

Thanks for your reply.

I included Modified date in my column and used following formula to get the desired result:

CALCULATE(
    COUNTROWS('Table1'),
    FILTER(Table1','Table1'[User]='Table2'User]),
    FILTER(ALLSELECTED('Table1'),'Table1'[IsReviewed]="Yes"),
    FILTER('Table1','Table1'[ModifiedDate] <= TODAY() - WEEKDAY(TODAY() -3))
)

Cheers,

Sumit