Creating a metric to show whether refresh has been pressed in the current month

[KL007]

Hi all,

I'm trying to create a visualisation to show the users of my BI Dashboard that the dataset has been refreshed this month. My idea was to use a table that contains the Column "Last Refresh" that updates it to the current date whenever it is refreshed. Then create a new table that checks wether the "Last Refresh" is within the current month, if it is then "Last Refresh Status" = 1, if not 0 (or something like that). 

For that I'm using the following DAX code, but the underlined values all contain errors (incorrect parameter type/cannot find):

Status Table =

ADDCOLUMNS(

    DimDate,

    "Last Refresh",

    RELATED('Monthly Refresh'[Monthly Refresh Month]),

    "Refresh Status Measure",

    IF([CurrentMonth] = 1 && [Last Refresh] >= STARTOFMONTH(TODAY()), "Green", "Red")

)

Any suggestions on how to improve this?

Thanks,

KL007

[manvishah17 · Accepted solution]

Hi  ,You can try this measure

Status Table =
ADDCOLUMNS(
DimDate,
"Last Refresh",
CALCULATE(MAX('Monthly Refresh'[Monthly Refresh Month])),
"CurrentMonth",
IF(
YEAR(CALCULATE(MAX('Monthly Refresh'[Monthly Refresh Month]))) = YEAR(TODAY()) &&
MONTH(CALCULATE(MAX('Monthly Refresh'[Monthly Refresh Month]))) = MONTH(TODAY()),
1,
0
),
"Refresh Status Measure",
IF(
[CurrentMonth] = 1 && [Last Refresh] >= STARTOFMONTH(TODAY()),
"Green",
"Red"
)
)

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[halfglassdarkly]

I could be wrong but I suspect RELATED wouldn't work here as 'Status Table' isn't going to have any relationship to 'Monthly Refresh' until after the table is calculated and you create the relationship. You could try:

    "Last Refresh",

    Max('Monthly Refresh'[Monthly Refresh Month]),