Forum Discussion

John2's avatar
John2
Frequent Visitor
2 years ago
Solved

Calculated column formula

I have a table called asset with ids and last report time of each id.  I want to get the ids which were reported in the last 24hrs, last 7 days, last 8-30 days and not reported for more than 30 days. Also, I want to get the unique ids since some ids are repeated.

Now, I have made a measure for each but in the dashboard when I am clicking on the measure visual, it has no effect on the other visuals present in the dashboard. How to go about it? Should I make a calculated column. If so what is the formula for the same? I have used formula of : 

Last_24_hr =
   if([Last Report Time] >= now() - 1 && [Last Report Time] <= now(), 1, 0)
the problem here is it doesnt take into account the unique ids.      what to do???
 

My dax measures:

Last_24_hr =

CALCULATE(
DISTINCTCOUNT(assets[ID]),
FILTER(assets, assets[Last Report Time] >= now() - 1 && assets[Last Report Time].[Date] <= now())
)


Last_7_Days =
CALCULATE(
DISTINCTCOUNT(assets[ID]),
FILTER(assets, assets[Last Report Time].[Date] >= TODAY() - 7 && assets[Last Report Time].[Date] <= TODAY() - 1)
)

8_to_30_Days =
CALCULATE(
DISTINCTCOUNT(assets[ID]),
FILTER(assets, assets[Last Report Time].[Date] >= TODAY() - 30 && assets[Last Report Time].[Date] <= TODAY() - 😎
)

Morethan_30_Days =
CALCULATE(
DISTINCTCOUNT(assets[ID]),
FILTER(assets, assets[Last Report Time].[Date] <= TODAY() - 30)
)

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    HI John2,

    So you mean you want to use one calculate field to get dynamic last n period based on current system date? If the is the case, I'd like to suggest you write multiple conditions in variable and add matched conditions flags to return the period levels.
    For example:
    Over one month level 1, last month level 2, last week level 3, last 3 days level 4, yesterday level 5. (the high level also matches the period tag that less than it)
    You can use 'less than or equal to' mode to filter on level to display all match records. Also, you can add a new calculated column to show the description of these levels.

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    HI John2,

    So you mean you want to use one calculate field to get dynamic last n period based on current system date? If the is the case, I'd like to suggest you write multiple conditions in variable and add matched conditions flags to return the period levels.
    For example:
    Over one month level 1, last month level 2, last week level 3, last 3 days level 4, yesterday level 5. (the high level also matches the period tag that less than it)
    You can use 'less than or equal to' mode to filter on level to display all match records. Also, you can add a new calculated column to show the description of these levels.

    Regards,

    Xiaoxin Sheng