topic calculated accumulated values by quarter in DAX Commands and Tips

calculated accumulated values by quarter

Emerald70 — Fri, 11 Oct 2024 15:35:51 GMT

Document	Date	Quarter
Doc1	1/22/2023	Q1-2023
Doc1	2/11/2023	Q1-2023
Doc2	3/12/2023	Q1-2023
Doc3	4/13/2023	Q2-2023
Doc4	5/14/2023	Q2-2023
Doc4	6/15/2023	Q2-2023
Doc4	10/16/2023	Q3-2023
Doc4	11/17/2023	Q3-2023
Doc4	12/18/2023	Q3-2023
Doc100	10/16/2022	Q3-2022
Doc100	11/17/2022	Q3-2022
Doc100	12/18/2022	Q3-2022
Doc4545	12/18/2022	Q3-2022

I have a quarter slicer selection and I dont have a date and to calculate the
KPI : no of unique docs from the year 2022 to selected quarter
Example: if I select the Q2-2023 on the slicer, then the no of unique documents to be calculated from 2022 to 2023
output is : 6
Please help me to write a dax ?

Re: calculated accumulated values by quarter

Gabry — Fri, 11 Oct 2024 15:56:21 GMT

Hello Emerald70 ,

Measure = Var _slicer = selectedvalue('Table'[Quarter]) var _maxdate= Calculate (max('Table'[Date]), 'Table'[Quarter] = _slicer, all('Table')) return calculate(DISTINCTCOUNT('Table'[Document]), 'Table'[Date]<=_maxdate, all('Table'))

Let me know if this help

Re: calculated accumulated values by quarter

Emerald70 — Mon, 14 Oct 2024 08:19:26 GMT

The solution provided is not working

Re: calculated accumulated values by quarter

Gabry — Mon, 14 Oct 2024 08:33:13 GMT

Why? I tested it and it works

You have to check if data column is date data type.
Also you asked to distinct count the documents, so to me it works as you asked. Let me know

Re: calculated accumulated values by quarter

Emerald70 — Mon, 14 Oct 2024 09:12:05 GMT

it is date type only but still does not show the right result, because whatever the quarter I select it gives the same output as 6, if I select Q3-2022 then it should give 2 but it gives 6 ,could you share your PBI file

Re: calculated accumulated values by quarter

Gabry — Mon, 14 Oct 2024 12:13:29 GMT

I think you might have done some mistake. Did you adjusted the measure to get the correct value of the slicer?

Here is the pbix.

If you still can't solve the issue, attach your pbix, I'll take a look