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.
Hi I'm calculating a running total for each quarter each year and I would want it to stop and restart at the end of each year.
This is for TRACKED_LOANS. My current calculation is:
Solved! Go to Solution.
To use this Time Intelligence function you need a proper Date table and reference the column that has marked as the date column (not possible with a column like you showed in your picture).
If you're not prepared to work with such a table, you'd have to add a year-filter into your original measure like so:
TRACKED_LOANS running total in Year Quarter =
CALCULATE(
SUM('FACT_Unique Tracked Loans'[TRACKED_LOANS]),
FILTER(
ALLSELECTED('DIM_Date'[Year Quarter]),
ISONORAFTER('DIM_Date'[Year Quarter], MAX('DIM_Date'[Year Quarter]), DESC)
),
FILTER(
ALL('DIM_Date'[Year]),
'DIM_Date'[Year] = MAX('DIM_Date'[Year])
)
)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
hey There
When I adda slicer of the quarter year this breaks the calculation and this gives me the tracked loans again
That's due to the ALLSELECTED you've used. I strongly recommend to work with a proper date table, then you can use standard solutions for your problems.
Now you can try sth like this:
TRACKED_LOANS running total in Year Quarter =
CALCULATE(
SUM('FACT_Unique Tracked Loans'[TRACKED_LOANS]),
FILTER(
ALL('DIM_Date'[Year Quarter]),
'DIM_Date'[Year Quarter] <= MAX('DIM_Date'[Year Quarter])
),
FILTER(
ALL('DIM_Date'[Year]),
'DIM_Date'[Year] = MAX('DIM_Date'[Year])
)
)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello,
I just switched it to a date field in DIM_DATE and I'm still getting the same:
To use this Time Intelligence function you need a proper Date table and reference the column that has marked as the date column (not possible with a column like you showed in your picture).
If you're not prepared to work with such a table, you'd have to add a year-filter into your original measure like so:
TRACKED_LOANS running total in Year Quarter =
CALCULATE(
SUM('FACT_Unique Tracked Loans'[TRACKED_LOANS]),
FILTER(
ALLSELECTED('DIM_Date'[Year Quarter]),
ISONORAFTER('DIM_Date'[Year Quarter], MAX('DIM_Date'[Year Quarter]), DESC)
),
FILTER(
ALL('DIM_Date'[Year]),
'DIM_Date'[Year] = MAX('DIM_Date'[Year])
)
)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thank you so much this worked like a charm !
I tried this solution and I'm getting just the TRACKED_LOANS
The DATESYTD-function has to reference the DIM_Date and not the Fact-table.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
You can use datesytd and totalytd, It will restart after year
YTD Sales = CALCULATE(SUM('FACT_Unique Tracked Loans'[TRACKED_LOANS])),DATESYTD(('DIM_Date'[Date])))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Hi @Anonymous
you could consider a more generich approach like so:
TRACKED_LOANS running total in Year Quarter =
CALCULATE(
SUM('FACT_Unique Tracked Loans'[TRACKED_LOANS]),
DATESYTD('DIM_Date'[Date]),
)
)
You might have to replace the "Date" by the name of the date-column in your DIM_Date.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |