DAX query to change the From date based on sysdate

[Charu]

Hi Everyone,

Have a good day 🙂

I come up with some logic for my date filter, please help me to write DAX formula for that.

the logic is Date Filter: Apr to June; July to Sep; Oct to Dec; Jan to March

in date filter to-date as sysdate and from date logic is here
1. if the system date is APRIL 10th then the from date should display 1st of April and our todate will be the sysdate,
2.if the sysdate is June then the from date should display from Apr 1st to till June system date.
3.Likewise, if sysdate is march then from date should display from jan1st to till system
help me how to write Dax for this logic.

Thanks in advance

[Anonymous]

The simpliest implemenation is to have a Flag field in your data (custom column) which does a check if the date in the current row is part of the current Quarter.  This field will contain either True or False.

From here, your filter can simply be to say "only show records where that field is True".  An example dax for this calculated column might be:

isCurrentQ = IF(
	YEAR([DateField]) = YEAR(TODAY()),
	CEILING(DIVIDE(MONTH([DateField]), 3), 1) = CEILING(DIVIDE(MONTH(TODAY()), 3), 1),
	FALSE
)

[Charu]

Hi @Anonymous

This to a great idea but I need to use date slicer that to BETWEEN that is From date and To-date.so that user can select the date which they want to see the data on the particular date too.

[Anonymous]

By changing the code to this:

Quarter = "Q" & CEILING(DIVIDE(MONTH([Date]), 3), 1) & "-" & YEAR([Date]) 

You would embed the Quarter into your data instead.  This won't appear as a date slicer, but at least they will get a Quaterly picklist to choose from

[Charu]

Hi @Anonymous

Yeah, I agree but when I use this quarter formula It will show q2 right but my client wants to display as Q1 which means financial year wise so that I came up with date filter having this logic.

To display the Qtr filter like how they are asking I have tried and couldn't achieve it. If you could help me out please see my other post and help me if that is possible.

http://community.powerbi.com/t5/Desktop/How-to-create-calculated-column-to-display-current-quarter-amp/m-p/391225#M178329 

Requesting you to have a look at the complete chain, thank you @Anonymous

[Anonymous]

Nah. We can achieve that much simpler!  Just do a looping + to the value.  So if Q2 is really Q1, i just need to minus the value by 1.  Any values that are 0 or less, we increase by 4.

So this becomes:

Quarter = var QValue = CEILING(DIVIDE(MONTH([Date]), 3), 1)
var AdjAmount = 1
var AdjValue = if (QValue - AdjAmount <=0, QValue - AdjAmount + 4, QValue - AdjAmount)
RETURN
"Q" & AdjValue & "-" & YEAR([Date]) 

[Charu]

Hi @Anonymous

For your reference please see this image hope you will understand my requirement.

Please do the needful.Thank you.

[Anonymous]

@Charu  There is a conflicting requirement in your description so far:

1. You stated you want to show just the current quarter in your date slicer.  The IsCurrent solution will allow you to only select the current slicer.

2. You stated you wanted the user to select the quarter instead.  The alternate solution was provided to give you a picklist of quarters.

Solution 1 and Solution 2 are mutually exclusive.

In the very last post you are looking at the 'isCurrent' solution i suggested earlier, whereby you filter your report page using the isCurrent field.

[Charu]

Hi @Anonymous

Sorry for the inconvenience, I will explain my requirement clearly.

Ultimately I need either measure/column not the custom table

point 1: I need to use date filter in which I need to display quarterly data example if we are in the month of may (quarter1) date filter has to display (Apr1st to may present date), whereas I'm using direct query mode I'm unable to create the custom date table, so that I'm creating one period table with the fields Date, Year, MonthNo, Quarter and data in that is like (4/11/2018;2018;4;Qtr 1),here I need either calculated column or measure to display the date based on quarter.

Is there any way to create measure or column where I could use in date slicer visual?

Please tell me whether this scenario is possible or not?

Point2: I need to display current quarter by default Checked option in filter visual.Here Whenever quarter is changing based on that Qtr number should display but by default checked no manual check/uncheck.

For this also as I'm using Direct query mode so I couldn't create custom table In oracle I have created one table with the fields  Date; Year, Month and data in it is like complete one year date (1/1/2018 to 12/31/2018).

Here I tried the code that you have suggested but it brings the complete list of qtr's in the filter visual but I need to display only the current qtr in my filter with default check option.

Note: As I'm using N of queries in my report approx 6tables, in addition, one Period table, so I have created the relationship between the Period table and all other 6tables date columns and in the filter, and I'm making use of  the created column/measure from Period table which calculates current quarter but the issue is data are not filtering based on selection

just to display current quarter I tried the below formula

IsCurrent = IF(YEAR ( 'Period Tbl'[START_DATE] ) = YEAR ( TODAY () )
&& MONTH ('Period Tbl'[START_DATE] ) = MONTH ( TODAY () ),
'Period Tbl'[QUARTER_NUM],"OTHER"
)

[Anonymous]

Ok, I have an idea.  We can combine the solutions.  Keep the isCurrentQ column as we had it previously.  Next i've modified the Quarter column for 2 changes:

Firstly, the ordering of the last one would have made sorting a bit terrible.  So i've rearranged to make that better.

Secondly, I'm having this code be aware of whether it is the current quarter.  If it is, it will set a specific moniker to say it is current.

Quarter = var QValue = CEILING(DIVIDE(MONTH([Date]), 3), 1)
var AdjAmount = 1
var AdjValue = if (QValue - AdjAmount <=0, QValue - AdjAmount + 4, QValue - AdjAmount)
RETURN
IF(
	isCurrentQ,
	"Current",
	YEAR([Date]) & "-" &   "Q" & AdjValue
)

Now if you set your filter to be "Current" as default on this field, this selection will stick around but what gets classified as "Current" will be dynamic.

I also named it differently, incase you wanted to keep the existing Quarter Field on any report displays.  It would look like:

Quarter = var QValue = CEILING(DIVIDE(MONTH([Date]), 3), 1)
var AdjAmount = 1
var AdjValue = if (QValue - AdjAmount <=0, QValue - AdjAmount + 4, QValue - AdjAmount)
RETURN
YEAR([Date]) & "-" &   "Q" & AdjValue

[Anonymous]

This could also probably done through the Power Query section too.  It might be faster in there, so if you get some performance problems, thats where i'd try to optimise.

[Charu]

Hi @Anonymous

with some sample data, I have tested as per your suggestion, In my sample data I'm having April, may (qtr1)and July(qtr2), as current quarter belongs to April, may, June in Qtr 1 when I filter Current from the filter it's not showing me the may records.

Here I have attached the Pbix and sample data, please correct me where I'm wrong.

SAMPLE DATA.XLSX

currentqtr.pbix

[Anonymous]

Sorry @Charu, i'm unable to download and open such files.  Our company policies prevent me from doing this.  I won't be able to assist in that portion of your troubleshooting.

Try pulling as much data as you can into different Tables & Matrix visuals.  I often do that on test pages to see what i can discern from the data.

Best of luck!

[Phil_Seamark]

Hi @Charu

Does this calculated table help?

It will dynamically generated rows based on the system date back to the 1st of each quarter.   I have included three columns as I'm not 100% sure what you need and they might help

Table = 
VAR ToDate = TODAY()
VAR SysMonth = MONTH(ToDate)
VAR FromMonth = DATE(YEAR(ToDate), SysMonth - MOD(MONTH(ToDate)-1,3) , 1)
RETURN
    ADDCOLUMNS(
        CALENDAR( FromMonth , ToDate),
        "From Date" , FromMonth ,
        "To Date" , ToDate  )

[Charu]

Hi @Phil_Seamark

Thank you so much for this very quick response.Really this formula will be helpful later

Sorry I forget one thing to point out that is I'm Using Direct query mode and connected 5different tables each has a different query but in each table one date column will be there.

I tried to create a custom date table in direct query mode I couldn't hope it is not possible too, so please suggest me how to make use of this formula that you have given