Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tmears
Helper III
Helper III

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. 

 

I have in my calanader table our fiscal year, with correct data, but would like a marker (indicator) so i can use this on Visual level Filters

 

I am sure this will be very easy for someone more experience than this learner (ME)!!!

 

Many thanks


Tim

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

12 REPLIES 12
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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)

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

That's correct but I need fiscal quarter not month, sorry might have explained poorly

Here's a go at that one:

 

IsCurrentFiscalQuarter = 

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

VAR fiscal_quarter_number_today = ROUNDUP(fiscal_month_today/3,0)

VAR fiscal_quarter_display_today = "FQ" & FORMAT(fiscal_quarter_number_today,"0")

RETURN IF(fiscal_quarter_display_today = [FiscalQuarterDisplay],1,0)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

you are an absoltule star, my only problem now is that as the date table is across multiple year it idnetifies the correct fiscal quarter but identify all QTR4's in all year ie 2014,2015,2016,2017,

 

I feel i am taking liabilities abit now so will try and do some more research to work out if i can work this final problem out myself, as dont want to take advantage of your good nature,!!! thank you so much for your help

 

Tim

IsCurrentFiscalQuarter = 

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

VAR fiscal_quarter_number_today = ROUNDUP(fiscal_month_today/3,0)

VAR fiscal_quarter_display_today = "FQ" & FORMAT(fiscal_quarter_number_today,"0")

RETURN IF(fiscal_quarter_display_today = [FiscalQuarterDisplay] && YEAR([Date]) = YEAR(TODAY()),1,0)

Just a bit of a combination of the previous few formulas. 🙂


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

you are a legend!!  thank you so much,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.