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 all
I have a report wich time slicer is "This Year" , so it has dates with no transactions.
I want to calculate a few measures according to last quarter with transactions, and I need it dynamic with option be filtered by other monthly visualizations inside the report.
In other words by default, I need it by a quarter of the last transaction, until it sliced by another month inside a report.
May you help me to write a dax code to make it work?
Thanks a lot, Rita
Solved! Go to Solution.
Hi @Ritaf ,
I think the effect you need is to return the latest QTD with data if there is no data in the date selected by the slicer
1. Here is my sample data.
2. Create a calendar table.
Calendar = CALENDAR(DATE(2020,1,1),DATE(2021,12,31))
3. Create measures.
QTD =
TOTALQTD ( SUM ( 'Table'[Value] ), 'Calendar'[Date] )
QTD Value =
VAR LastNonBlankDate =
CALCULATE (
MAX ( 'Calendar'[Date] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
&& [QTD] <> BLANK ()
)
)
RETURN
CALCULATE (
[QTD],
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = LastNonBlankDate )
)
4. The result is as follows.
You can check more details from here.
Best regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Ritaf ,
I think the effect you need is to return the latest QTD with data if there is no data in the date selected by the slicer
1. Here is my sample data.
2. Create a calendar table.
Calendar = CALENDAR(DATE(2020,1,1),DATE(2021,12,31))
3. Create measures.
QTD =
TOTALQTD ( SUM ( 'Table'[Value] ), 'Calendar'[Date] )
QTD Value =
VAR LastNonBlankDate =
CALCULATE (
MAX ( 'Calendar'[Date] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
&& [QTD] <> BLANK ()
)
)
RETURN
CALCULATE (
[QTD],
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = LastNonBlankDate )
)
4. The result is as follows.
You can check more details from here.
Best regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much, for your understanding and assistance
This is exactly the solution I needed!❤️
@Ritaf - These might help: You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Otherwise, I agree with @Fowmy - Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
@Ritaf
Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?
_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |