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

[Ritaf]

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

[v-stephen-msft · Accepted 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.

[Ritaf]

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

[Greg_Deckler]

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

[Fowmy]

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

YouTube, LinkedIn