Help with same period, but the month is not finished

[acorcos]

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

[acorcos]

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

[CNENFRNL]

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"
)

[Tanushree_Kapse]

@acorcos ,

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


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

[Anonymous]

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

[acorcos]

Hi!

I got same result of all month

Thanks

[Anonymous]

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))

[Tanushree_Kapse]

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!

[acorcos]

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)

🙂