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.

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")

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

OK, so for FiscalMonth, I believe you would have something like:

 

IsFiscalMonth = 

VAR fiscal_month_today = If( Month(TODAY()) >= 4  , Month(TODAY()) - 3,Month(TODAY()) + 9 )

RETURN IF(fiscal_month_today = [FiscalMonth],1,0)

 

 

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

    thanks, thats works, but what i really need to see is if it is within the present fiscall quarter.  The end goal is to have total sales, but then put a visual filter on that it  to return if it within the present fiscall quarter, therfore on the 1st of April, this will reset to zero sales and start again.  Hope that makes sense

    our qtr 4 is Jan, Feb and March

     

    Really appriciate your help

     

    Tim

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

      OK, maybe I'm not understanding, but I think all you would need to do would be to set the visual filter to "IsFiscalMonth = 1" and you would have what you are looking for. I probably should have called that metric "IsCurrentFiscalMonth". Basically, if you are calculating FiscalMonth for the dates in your date column, that formula should return 1 for every date in the date table that has the same FiscalMonth as today's date. I *think*! If not, let me know what you are seeing.

      • tmears's avatar
        tmears
        Icon for Helper III rankHelper III
        9 years ago
        That's correct but I need fiscal quarter not month, sorry might have explained poorly