Update automatically the value of current date (today)

[ThuJa23]

Dear all,

I have a table as below, which:

- DATE DE is from individual Calendar table

- ATMONTH_ and ATLDF_ are from another table (which date in this table has already relationship with calendar table)

I would like to ask a DAX solution to show the value of ATDLF_ automatically when I open the dashboard everyday:

for example:

- today is 7.9.2021, so the result of ATLDF_ should show the value 5.

- tomorrow is 8.9.2021, so the result of ATLDF_ should show automatically the value 6

Do you know how can I solve this in Power BI by DAX measue?

Thanks for your all help!

[Fowmy]

@ThuJa23 

Can you try this:

Measure1 = 

CALCULATE (
    sum( tablename[ATLDF]),
    DatesTable[Date DE] = TODAY ()
)
    

[ThuJa23]

Hi @Fowmy ,

Thanks for your quick replies

I tried your code but this part

DatesTable[Date DE]

I cannot choose the column, because the drop list just showed me only the functions list, but not tables or columns as below 

Am I wrong somewhere?

Thanks for your helps!

[tex628]

Hi @ThuJa23, 

You should be able to do it witih something along the lines of:

Calculate ( MAX ( Table[ATLDF_] ) , Table[DATE DE] = TODAY() ) 

Assuming that your DATE DE column is distinct this should always return the ATLDF value related to today's date. 

Br,
J

[Fowmy]

@ThuJa23 

I corrected the code, please check now.

[ThuJa23]

Hi @tex628 

Thanks for your quick guidance,

I tried your DAX but it shows me the below error

As my data is from multidimension cubes (was created and managed in SQL Server), so connecting live in Power BI all the functions such as data type or calculated columns are hidden. Do your have any further solution for this? Thanks so much for your strong helps!

[tex628 · Accepted solution]

Alright! Depending of the date formatting is matching the first measure might work, otherwise try the second:

Measure =
Calculate ( MAX ( Table[ATLDF_] ) , 
Table[DATE DE] = LEFT( FORMAT(TODAY() , "" ) , 10 )
)

Measure =  
VAR Day_ = IF( DAY(TODAY()) < 10 , "0" & FORMAT(DAY(TODAY()),"") , FORMAT(DAY(TODAY()),""))
VAR Month_ = IF( MONTH(TODAY()) < 10 , "0" & FORMAT(MONTH(TODAY()),"") , FORMAT(MONTH(TODAY()),""))
VAR Year_ = FORMAT(YEAR(TODAY()),"")
Return 
Calculate ( MAX ( Table[ATLDF_] ) , 
Table[DATE DE] = Day_ & "." & Month_ & "." & Year_
)

Br,
J

[ThuJa23]

Hi @tex628 ,

I back here to hope your helps on this continue. As last time I was successful to display work days and days gone. However today is beginning of new month  (1/10/2021), but I still put my filter for month is in Semtember and the days gone display -1. I would like to have it display automatically as 22 (the total of working days, when the current day is last day of month). 

How could I do it? Could you please help me extra in this.

Sorry for this inconvenience, as I am really new in DAX. 

Thanks so much for your helps!

Thu

[tex628]

Do i understand you correctly that you want the display the ATLDF of the previous day whenever the current date is the first of a month? 

Br,
Johannes

[ThuJa23]

Hi @tex628 ,

I answered as below, do you have any idea about my issue? 

-------

Yes correctly. I'm sorry that I didn't make clear at beginning. Because we are looking sales of yesterday everyday. Or at 1st day of new month will display the last working day of last month. 

And ATLDF is available to count down the days gone. From your 2nd codes, I did - 1 after "MAX (Table[ATLDF_)"... to let the days back 1 day. It works well until today is 2.10.2021 (Saturday)

Return 
Calculate ( MAX ( Table[ATLDF_] ) , 
Table[DATE DE] = Day_ & "." & Month_ & "." & Year_
)

  The problem happen today as 1st weekend, you can see in the table of ATLDF, Saturday and Sunday have the same days gone as Friday, and in October the 1st weekend showed 1 in ATLDF as in Friday (1st working day of month). And with my adding -1 to the formular, it make "days gone" become 0 and break all other charts which calculated relevant to days gone. I do not know how to fix this one. 

As we did in Excel, it's easier that I just calculate days gone by doing vlookup on column ATLDF of this table, but in PBI because of live connect to multidimension cubes I cannot do vlookup. 

 I know it' s complicated to understand. Sorry about this. 

Thanks so much for your patience!

Thu

[tex628]

Hey! 

Try this:

Measure =  
VAR Yesterday = TODAY()-1 
VAR Day_ = IF( DAY(Yesterday) < 10 , "0" & FORMAT(DAY(Yesterday),"") , FORMAT(DAY(Yesterday),""))
VAR Month_ = IF( MONTH(Yesterday) < 10 , "0" & FORMAT(MONTH(Yesterday),"") , FORMAT(MONTH(Yesterday),""))
VAR Year_ = FORMAT(YEAR(Yesterday),"")
Return 
Calculate ( MAX ( Table[ATLDF_] ) , 
Table[DATE DE] = Day_ & "." & Month_ & "." & Year_
)

 
It should calculate the ATLDF of the previous day instead of today, which i believe should resolve your issue! 

I'm not active here daily so feel free to reach out on my linkedin if I'm not responing! 🙂 

Br, 
J

[ThuJa23]

Hi @tex628 ,

Thanks so much for your replies. It works well now. And sorry for annoying you. 😞

I did connection with you on LinkedIn.

[tex628]

Haha dont worry! I'm glad it worked out!

/ J

[ThuJa23]

hi @tex628 ,

Yes correctly. I'm sorry that I didn't make clear at beginning. Because we are looking sales of yesterday everyday. Or at 1st day of new month will display the last working day of last month. 

And ATLDF is available to count down the days gone. From your 2nd codes, I did - 1 after "MAX (Table[ATLDF_)"... to let the days back 1 day. It works well until today is 2.10.2021 (Saturday)

Return 
Calculate ( MAX ( Table[ATLDF_] ) , 
Table[DATE DE] = Day_ & "." & Month_ & "." & Year_
)

  The problem happen today as 1st weekend, you can see in the table of ATLDF, Saturday and Sunday have the same days gone as Friday, and in October the 1st weekend showed 1 in ATLDF as in Friday (1st working day of month). And with my adding -1 to the formular, it make "days gone" become 0 and break all other charts which calculated relevant to days gone. I do not know how to fix this one. 

As we did in Excel, it's easier that I just calculate days gone by doing vlookup on column ATLDF of this table, but in PBI because of live connect to multidimension cubes I cannot do vlookup. 

 I know it' s complicated to understand. Sorry about this. 

Thanks so much for your patience!

Thu

[ThuJa23]

Hi @tex628 ,

Perfect! The second code works to me, the first one just shows (blank) in value.

Thanks so much for your helps!

[tex628]

Wonderfull!

Br,
J