Forum Discussion

tmears's avatar
tmears
Icon for Helper III rankHelper III
9 years ago
Solved

Display this week & this month's data filter/Flag

Hi   I would like to put a marker in my Calander table which will change dynmaicly according to the present date.  I would like a marker for today, this week, this month, this quater and year.    ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    Generally this is done with an "IsToday" kind of column. So, assuming that you have a Calendar/Date table like:

     

    Date

    1/1/2017

     

    You could build a column like:

     

    IsToday = IF(DAY([Date]) = DAY(TODAY()) && MONTH([Date]) = MONTH(TODAY()) && YEAR([Date]) = YEAR(TODAY()),1,0)

    There are variations on this theme depending on your data format but this is the general gist of things. Your other flags would be similar formulas.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago

Generally this is done with an "IsToday" kind of column. So, assuming that you have a Calendar/Date table like:

 

Date

1/1/2017

 

You could build a column like:

 

IsToday = IF(DAY([Date]) = DAY(TODAY()) && MONTH([Date]) = MONTH(TODAY()) && YEAR([Date]) = YEAR(TODAY()),1,0)

There are variations on this theme depending on your data format but this is the general gist of things. Your other flags would be similar formulas.

  • tmears's avatar
    tmears
    Icon for Helper III rankHelper III
    9 years ago

    Brilliant many thanks

     

    i also have a calcualted column with our fiscal year and fiscal quarter calcualted, would i be able to use this to return wether today in in the present fiscal year and fiscal quarter??

     

    Thanks so much for your help


    Tim

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      9 years ago

      I can't imagine why not, is the calculated column in the Query Editor or a DAX column? And what are the formulas?

      • tmears's avatar
        tmears
        Icon for Helper III rankHelper III
        9 years ago

        its a dax column, i have the following columns:

         

        FiscalMonth = (If( Month([Date]) >= 4  , Month([Date]) - 3,Month([Date]) + 9 ))

        FiscalYearNumber = If( Month([Date]) >= 4  , Year([Date]),Year([Date]) -1 )

        FiscalYearDisplay = "FY"&Right(Format([FiscalYearNumber],"0#"),2)&"-"&Right(Format([FiscalYearNumber]+1,"0#"),2)

        FiscalQuarterNumber = ROUNDUP([FiscalMonth]/3,0)

        FiscalQuarterDisplay = "FQ" & format([FiscalQuarterNumber],"0")