topic Re: Previous QTD as of today for each quarter in DAX Commands and Tips

Previous QTD as of today for each quarter

Radha2024 — Wed, 19 Jun 2024 06:40:05 GMT

Help me to create a Dax measure to show the Previous QTD actuals as of day(today) for each quarter. I have date , amount columns

for example today is 6/19/2024 so we would like to see

 2024-Q1 data from start of quarter to till 3/19/2024

 2023-Q4 data from start of quarter to till 12/19/2023

 2023-Q3 data from start of quarter to till 9/19/2023

 2023-Q2 data from start of quarter to till till 6/19/2023

Currently, I am using this measure. But this is giving Total QTD.Previous Quarter QTD Actuals should be less than QTD Rev

Previous Quarter QTD Actuals =

calculate(SUM(Amount]),DATEADD(filter(DATESQTD('Calendar'[DateVal]),Calendar'[DateVal]<TODAY()),-1,QUARTER))

Re: Previous QTD as of today for each quarter

Anonymous — Wed, 19 Jun 2024 10:02:39 GMT

Hi,Radha2024

I am glad to help you.

According to your description, you want to get Previous QTD as of today for each quarter?

If I understand you correctly, then you can refer to my solution.

Since you didn't give me some test data, I assumed two columns of data Date and Value.

Then a new column Column is added for calculating the month corresponding to the quarter it is in.

Column =

YEAR([Date]) & " " &

SWITCH(TRUE(),

MONTH([Date]) <=3 , "Qtr1",

MONTH([Date]) <=6 && MONTH([Date]) > 3 , "Qtr2",

MONTH([Date]) <=9 && MONTH([Date]) > 6, "Qtr3",

MONTH([Date]) <=12 && MONTH([Date]) > 9, "Qtr4"

)

After that, using a matrix, two metrics were created to represent both the current quarter and the previous quarter's values.

Current_Qtr = SUM('Table'[Value])

Previous_Qtr =

VAR _today = DAY(TODAY())

VAR _minDate = DATE(YEAR(MIN('Table'[Date])),MONTH(MIN('Table'[Date])),1)

VAR _maxDate = DATE(YEAR(MAX('Table'[Date])),MONTH(MAX('Table'[Date])),_today)

RETURN SUMX(FILTER('Table','Table'[Date] >= _minDate && 'Table'[Date] <= _maxDate),[Value])

Here is the result:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Previous QTD as of today for each quarter

Radha2024 — Wed, 19 Jun 2024 11:35:23 GMT

Thankyou Anonymous for taking time to hep me.
according to your dataset.... I was expecting 2023Q2 Previous_QTR = 100.

it should show sum of vaue from date range 1/1/2023 to 3/19/2023 (Today is 19)
hence for 2023Q2 Previous_QTR is10+20+30+40 =100.

Re: Previous QTD as of today for each quarter

Anonymous — Fri, 21 Jun 2024 04:02:13 GMT

Hi,Radha2024

I'm wondering if you're looking at the wrong data in the image.

2023Q1 Previous_QTR is 10+20+30+40=100,

the corresponding data in the picture should be:

2023Q2 Previous_QTR is 60+70+80=210,

the corresponding data in the picture should be:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Previous QTD as of today for each quarter

Radha2024 — Fri, 21 Jun 2024 09:47:01 GMT

Hell0 Anonymous
thankyou, somehow your solution worked but I have altered that to my need. below I have mentioned the measure I have used. But the column , row subtotals, Totals aren't matching.
I have filtered the matrix to show past 4 Quaters. is there anything that I need to do ? to match totals.

VAR _today =

DAY ( TODAY () )

VAR _minDate =

DATE ( YEAR ( MIN ( DimCalendar[DateVal] ) ), MONTH ( MIN ( DimCalendar[DateVal] ) ), 1 )

VAR _maxDate =

DATE ( YEAR ( MAX ( DimCalendar[DateVal] ) ), MONTH ( MAX ( DimCalendar[DateVal] ) ), _today )

RETURN

CALCULATE(

CALCULATE (

SUM ( [Amount] ),

FILTER (

DimCalendar,

DimCalendar[DateVal] >= _minDate

&& DimCalendar[DateVal] <= _maxDate

FILTER ( DimCalendar, DimCalendar[DateVal] <= TODAY () )

)

Re: Previous QTD as of today for each quarter

Anonymous — Thu, 18 Jul 2024 09:24:59 GMT

Hi,Radha2024

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.