topic Re: DAX function to register MTD, QTD and other giving wrong results. in DAX Commands and Tips

DAX function to register MTD, QTD and other giving wrong results.

Elieazar — Thu, 09 May 2024 14:39:40 GMT

Hello,

I am trying to create a data slicer with various period filters such as MTD, QTD, YTD ...
I have already created a specific table that gives me 12 rows with each row being the first of each month from January till December of 2024.
However, when i use each periodic function, it's giving me the wrong result as if the end date is December 2024...
For example, this is the DAX function for each period :

Union(

ADDCOLUMNS(DATESMTD('Période Comptable'[Mois]),"Période","MTD"),

ADDCOLUMNS(DATESQTD('Période Comptable'[Mois]),"Période","QTD"),

ADDCOLUMNS(DATESYTD('Période Comptable'[Mois]),"Période","YTD")

)
But as you can see in the attached picture, it shows MTD row as December 2024, QTD it's giving me the last quarter of the year and YTD it's giving me the entire year...
Help pleasse.

Thank you,

Elie AZAR

Re: DAX function to register MTD, QTD and other giving wrong results.

Anonymous — Fri, 10 May 2024 01:26:12 GMT

Hi Elieazar

The first thing to check is whether your date table ('Période Comptable') is properly set up:
Continuous Dates: Your date table should ideally have a row for every single day in the year, not just the first of each month. Date intelligence functions expect a full range of dates to calculate periods correctly.

Mark as Date Table: In Power BI, ensure that your date table is marked as a date table. You can do this by going to the "Modeling" tab and selecting "Mark as Date Table", then choose the column that has the date.

If the above suggestions don't help you solve the problem, please provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny

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

Re: DAX function to register MTD, QTD and other giving wrong results.

Elieazar — Fri, 10 May 2024 08:01:53 GMT

Hello Jayleny,

Thank you for your reply and sorry for my late response.

Here are my responses :

My date table is marked as a date so no problems there.
I tried a test with a row for each day of the year but the result was wrong as if i was in december 2024 like in my explanation but for a longer period

As for what i want :

I wand to be able to create a table with 2 columns :
1. the first column is the date ranges
2. the 2nd is MTD, QTD, YTD and other specifics i can add on one the first ones are functional. Evidentally the MTD, QTD and YTD should have the end period of today and not december 2024.
Eventually, i want to create a slicer with this MTD, QTD and YTD table (cf. attached picture)

Please find the PBIx file with just the dates as well as the new table i tried to create.

Thank you in advance for your help,

Elie AZAR