topic Re: Quarter Days data in DAX Commands and Tips

Quarter Days data

Anonymous — Tue, 21 Feb 2023 08:15:26 GMT

Hi All,

I have a visual in my report where I'm showing data Q/Q like below.

What bussiness wants is that I should show the exact same day's data for each quarter for example today is 21st Feb and 21st day of the quarter for me so each quarter in the visual should show data for the first 21 days only instead of showing the complete consolidated data for that quarter.

I have the fiscal day column in my dataset. How should I get this thing done?

I have a measure created as well which I'm showing against these quarters. the values is ARR.

so I want something like

DAX = calculate([ARR],fiscal_day_of_quarter <= Today()).

But this is not giving the correct info here.

Re: Quarter Days data

Anonymous — Tue, 21 Feb 2023 09:29:46 GMT

I have created a Date table as well and now just want to know how to proceed on the request.

What DAX function to use??

Re: Quarter Days data

Anonymous — Tue, 21 Feb 2023 15:05:08 GMT

I want something like below where I can get values for 1st day of the quarter to the present day of the quarter.

How to get this through DAX so that it gets dymanic??

amitchandak Greg_Deckler FreemanZ any help here?

Instead of the hard coded value of "21" I want the current quarter day number there

Re: Quarter Days data

andhiii079845 — Tue, 28 Feb 2023 13:15:40 GMT

You can Calculated before the current date like:

My Measure = 
VAR currentday = DAY(TODAY())
RETURN DAX = calculate([ARR],fiscal_day_of_quarter <= currentday)

Re: Quarter Days data

Anonymous — Tue, 28 Feb 2023 13:56:38 GMT

Thanks for the response but the calculation will not solve the issue. For example tommorrow will be 1st of March and that will be 29th Day of my quarter but the variable will pick the day as 1 and the result will calculate the value based on the day = 1 for tommorrow. However it should be like 29th Day of the quarter.

Re: Quarter Days data

andhiii079845 — Tue, 28 Feb 2023 14:20:24 GMT

Your problem is only to get for the 1st of March (if this day is today) the value (fiscal_day_of_quarter field) 29 from the Dimtable? Is this right? The both tables are in a relationship?
You can try like this as a example:

DimTable[Day] is your fiscal_day_of_quarter column in the DateTable.

Fact is your second table.

Measure = 
Var currentday = TODAY()
Var day = CALCULATE(VALUES(DimTable[Day]),FILTER(RELATEDTABLE('Fact'),'Fact'[Date]=currentday))

RETURN Day

Re: Quarter Days data

Anonymous — Tue, 28 Feb 2023 15:06:16 GMT

I tried the below DAX but its not working

Measure =

Var currentday = [Today]

Var day = CALCULATE(VALUES(D_DATE[FISCAL_DAY_OF_QUARTER]),FILTER(RELATEDTABLE(F_ACV),F_ACV[CLOSE_DATE]=currentday))

RETURN

CALCULATE([ACV],D_DATE[FISCAL_DAY_OF_QUARTER] = day)

Re: Quarter Days data

Anonymous — Tue, 28 Feb 2023 15:13:39 GMT

I'm using the below calculation for the time being to get the desired result. the issue with this DAX is I'll have to add the previous months days to the calculation like tommorow I'll replace 0 with 28 in my calculation.

Re: Quarter Days data

andhiii079845 — Tue, 28 Feb 2023 15:20:40 GMT

WHY [TODAY] instead of TODAY() ?

Re: Quarter Days data

Anonymous — Wed, 01 Mar 2023 12:48:10 GMT

I'm using date direct from my DB.

Re: Quarter Days data

andhiii079845 — Wed, 01 Mar 2023 12:53:58 GMT

Please try my solution first. you also use today() in your first post.
What is [TODAY] ? a Column with one value?
Than you can try something like Var currentday = calculate(VALUES([Today]))

Re: Quarter Days data

Anonymous — Wed, 01 Mar 2023 13:17:53 GMT

The day number is coming correct using your measure but how to calculate the total fact amount i.e ACV here, DAX doesnt allow me to use calculate with the measure created.

Re: Quarter Days data

andhiii079845 — Wed, 01 Mar 2023 13:52:54 GMT

Sorry, is to confusing for me.