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 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).
Solved! Go to 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]
))
@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.
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]
))
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |