How to show certain value if Today () is greater than certain Date.

[Anonymous]

Hello,

I am trying to add a column (January) to my table. When Today is greater than 31.01 (always the end of the Month) then it give me the actual value (Column "Zeitraum" only IST 2021), and when it is greater than 31.01 it should just give me the value)

So what I try to do, is to make a matrix table to show actuals when the month is passed otherwise the forecast. In my table actuals are called "IST 2021".

Thank you in forward.

[selimovd · Accepted solution]

@Anonymous The picture you post here is from the Power Query editor. There you have to write in M.

The "if(TODAY()..." should work as calculated column in Power BI, but not in Power Query as there you need to write the query in M as mentioned.

So try to add the column in DAX and hopefully it should work.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[selimovd · Accepted solution]

Hey @Anonymous ,

yes, you can always make it dynamically.

If you always want to adapt the date to the current year, you could do it like that:

January =
IF(
    TODAY() >= DATE( YEAR( TODAY() ), 01, 31 ),
    CALCULATE(
        [Sum All],
        Allend1[Zeitraum] = "IST 2021"
    ),
    [Sum All]
)

Best regards

Denis 

[selimovd]

Hey @Anonymous ,

do you need the column [Measure 4.1]?

For me it seems like that column is causing the circular dependency. Do you have a column with a unique index for each row? That could also help you with the dependency.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi @selimovd,

thanks. I removed the measure, but now it shows me only 0. Maybe I should add a custom Column? I dont have column with a unique Index. I just have Column "Zeitraum" to filter Actual und BP2021 and the Column "Monat" to filter the Month (Date).

But then the formula does not work:

[selimovd · Accepted solution]

@Anonymous The picture you post here is from the Power Query editor. There you have to write in M.

The "if(TODAY()..." should work as calculated column in Power BI, but not in Power Query as there you need to write the query in M as mentioned.

So try to add the column in DAX and hopefully it should work.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd, I closed and opened the file and did it again, but this time I deleted the Measure 4.1 at first, and it works now.

I have another question. Is there a way to use instead of Date (2021,01,31) only the month and day or instead of 2021 the current year. Because otherwise I will need to change the formulas in 2022. In the Column "Monat" I just wrote the Day and the Month and formated it as a Date. I think the year will be changed automatically next year.

Thank you and best regards,

Albert

[selimovd · Accepted solution]

Hey @Anonymous ,

yes, you can always make it dynamically.

If you always want to adapt the date to the current year, you could do it like that:

January =
IF(
    TODAY() >= DATE( YEAR( TODAY() ), 01, 31 ),
    CALCULATE(
        [Sum All],
        Allend1[Zeitraum] = "IST 2021"
    ),
    [Sum All]
)

Best regards

Denis 

[Anonymous]

Hi @selimovd,

great, thank you very much, it works but when I try to add another column for month February it shows again the same error like before.

Any Idea, what does it mean?

please apologize for my questions. I really appreciate that you invest your time to help me.

[Anonymous]

So I tried to do it as measure and not as calculated column. I think it should work now.

I dont really understand whats the difference between calculated column and mesaure, the outcome is the same.

[selimovd · Accepted solution]

Hey @Anonymous ,

circular dependencies can be tricky sometimes. I think with an ALLEXCEPT and the columns you need for the formula you can solve that. This could possibly work:

January =
IF(
    TODAY() >= DATE( YEAR( TODAY() ), 01, 31 ),
    CALCULATE(
        [Sum All],
        Allend1[Zeitraum] = "IST 2021",
        ALLEXCEPT(Allend1, Allend1[Zeitraum])
    ),
    [Sum All]
)

Take a look at the following video, here Alberto Ferrari explains the issue:

https://www.youtube.com/watch?v=m-qhcDcNKrE

The short version:

Calculated Column is like a normal column in the table. It's generated when the file is loaded and will not change afterwards. So the value won't change to a slicer or selection. You can use the column then as a slicer.

Measure is dynamic, can change with every selection or change in a filter or slicer. But it always depends on the filter context. So if you say SUM(Sales[SalesAmount]) it shows the sales amount. When you put the products in a table it will show by product, if you put the country in the table, it will show by country, if you put the time in a table it will show by time. This is called the filtercontext and as the measure can change to the filter context this tool is very powerful.

But as it needs a filter context you cannot use it as a slicer. Makes sene, when you think about it. If you put SUM(Sales[SalesAmount]) in a slicer, should it show the values by country, by product or by year? For that reason it's not possible.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic