Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
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
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"
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@acorcos ,
SalesMTDLastYear = CALCULATE ( TTMMATUALSYS, SAMEPERIODLASTYEAR ( AC_TKTBYMM[DATE] ) )
SalesMTDLastYear2 = CALCULATE ( TTMMATUALNET, SAMEPERIODLASTYEAR ( AC_TKTBYMM[DATE] ) )
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
Hi!
I got same result of all month
Thanks
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))
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!
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)
🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |