Calculate amount using advance filter by date with few conditions

[bsas]

Hi All,

I need help with creating measure for amount calculation based on some conditions.

If I choose date in slicer, it should calculate all amounts by close date if close date <= date in slicer and close date >= StartOfQuarterDate of date in slicer. It should include all deal from the beginning of quarter till choosed date. 

How I should organize logic in this case? If I create relationship from calendar to deal table by date it is filtering only one month data.

test measure below:

__test revenue = 
CALCULATE(
    SUM('deal'[Booking Won]),
        FILTER('deal', 
                'deal'[isNewLogo] = "yes" ||
                'deal'[CloseDate] <= RELATED('calendar'[Date])||
                'deal'[CloseDate] >= RELATED('calendar'[startofQuarter]))

date in slicer is calculated table (calendarauto).

[amitchandak]

@bsas , join close date with date table and use datesqtd for QTD . that is what it looks like

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[bsas · Accepted solution]

Hi,

Thanks for your answer, I've managed to calculate this using my current model without any relationships to calendar table.

SelectedDate = CONVERT(SELECTEDVALUE('calendar'[Date],0),DATETIME)
StartQuarter = STARTOFQUARTER('calendar'[Date])

and used these measures in main measure

NewLogo booked Won from start of Quarter till selected date = 
 CALCULATE(
     SUM('dealsfact'[Booking Won]),
        FILTER('deals',
            'deals'[isNewLogo] = "yes" &&
            'deals'[CloseDate] <= [SelectedDate] &&
            'deals'[CloseDate] >= [StartQuarter]
          
            ))