topic Re: Help with same period, but the month is not finished in DAX Commands and Tips

Help with same period, but the month is not finished

acorcos — Tue, 07 Sep 2021 10:39:36 GMT

Hello community, I have tried some solutions that I saw but I got no success.

I have a simple table with DATE, COUNT and TKT_TYPE

Date = its a day, like 01/01/2021

Tkt_Type = There are two values here, System or Network

Count = total tickets opened on that day

Here how the table looks

Date | Count | Tkt_type

01/01/2021 | 5 | System

01/01/2021 | 3 | Network

02/01/2021 | 4 | System

02/01/2021 | 8 | Network

....

I need two infos:

1) Total tickets by type on this month (It works)

TTMMATUALSYS = TOTALMTD(SUM(AC_TKTBYMM[COUNT]),AC_TKTBYMM[DATE],AC_TKTBYMM[TKT_TYPE]="System")

TTMMATUALNET = TOTALMTD(SUM(AC_TKTBYMM[COUNT]),AC_TKTBYMM[DATE],AC_TKTBYMM[TKT_TYPE]="Network")

2) And now I need same result, but at the same period of last year. Today is 07/09/2021 so, i need from 01/09/2020 to 07/09/2020

I tried this

TTMMPASSSYS = TOTALMTD(SUM(AC_TKTBYMM[COUNT]),SAMEPERIODLASTYEAR(AC_TKTBYMM[DATE]),(AC_TKTBYMM[TKT_TYPE]="System"))

but the result is for all month.

Can please anyone help?

Thanks

Re: Help with same period, but the month is not finished

Tanushree_Kapse — Tue, 07 Sep 2021 11:00:33 GMT

Hi acorcos ,

Please try below measure:
PreviousyrMTD(System)= CALCULATE(TTMMATUALSYS ,PARALLELPERIOD(AC_TKTBYMM[DATE], -12, MONTH)

PreviousyrMTD(Network)= CALCULATE(TTMMATUALNET ,PARALLELPERIOD(AC_TKTBYMM[DATE], -12, MONTH)

I hope this helps!
Mark this as a solution, if it answered your question. Kudos are always appreciated.

Thanks!

Re: Help with same period, but the month is not finished

acorcos — Tue, 07 Sep 2021 11:05:54 GMT

Hello! Thanks, but the results show all month of sep/2020.

I just need sep/2020 but the end date must be same as today (07/09/2020)

🙂

Re: Help with same period, but the month is not finished

Anonymous — Tue, 07 Sep 2021 11:16:40 GMT

Hi acorcos

can you try this?

TTMMPASSSYS = CALCULATE(TOTALMTD(SUM(AC_TKTBYMM[COUNT]),(AC_TKTBYMM[DATE]),(AC_TKTBYMM[TKT_TYPE]="System")),SAMEPERIODLASTYEAR(AC_TKTBYMM[DATE]))

Regards,

Aditya

Re: Help with same period, but the month is not finished

Tanushree_Kapse — Tue, 07 Sep 2021 11:19:33 GMT

acorcos ,

SalesMTDLastYear =
CALCULATE ( TTMMATUALSYS, SAMEPERIODLASTYEAR ( AC_TKTBYMM[DATE] ) )


SalesMTDLastYear2 = CALCULATE ( TTMMATUALNET, SAMEPERIODLASTYEAR ( AC_TKTBYMM[DATE] ) )

Re: Help with same period, but the month is not finished

acorcos — Tue, 07 Sep 2021 11:20:11 GMT

Hi!

I got same result of all month

Thanks

Re: Help with same period, but the month is not finished

CNENFRNL — Tue, 07 Sep 2021 11:21:35 GMT

Use a date table if you want to make the best of time intelligence functions,

TTMMPASSSYS = CALCULATE( SUM( AC_TKTBYMM[COUNT] ), DATEADD( DATESMTD( DATES[Date] ), -1, YEAR ), AC_TKTBYMM[TKT_TYPE] = "System" )

Re: Help with same period, but the month is not finished

Anonymous — Tue, 07 Sep 2021 11:33:58 GMT

Can you please try with,

TTMMPASSSYS = CALCULATE(TOTALMTD(SUM(AC_TKTBYMM[COUNT]),(AC_TKTBYMM[DATE]),(AC_TKTBYMM[TKT_TYPE]="System")),PARALLELPERIOD(AC_TKTBYMM[DATE],-1,YEAR))

Re: Help with same period, but the month is not finished

acorcos — Tue, 07 Sep 2021 11:37:49 GMT

Just got same result till here, all month of sep/2020

Going to the oracle database, i run this:

SELECT SUM(COUNT) FROM AC_TKTBYMM WHERE DATE>=to_date('01/09/2020','DD/MM/YYYY') and DATE<=to_date('07/09/2020','DD/MM/YYYY') and tkt_Type='System'

The result is 24

If i just change the date 07/09/2020 to 30/09/2020 i got 147.

All tries till here show 147 on card