cancel
Showing results for
Did you mean:

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.

Responsive Resident

## QTD calculation based on last transaction ignoring "future dates" on slicer

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

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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.

Responsive Resident

Thank you so much, for your understanding and assistance
This is exactly the solution I needed!❤️

Super User

@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

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

@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?

_____________________________________
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 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors