Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi Friend,
Please help
Year | Quarter | Type | Sales | Sales QTD | Sales LQTD |
2018 | Q1 | A | 22242 | 22242 | |
2018 | Q2 | B | 6504 | 6504 | |
2018 | Q3 | C | 14942 | 14942 | |
2018 | Q4 | D | 22185 | 22185 | |
2019 | Q1 | A | 7686 | 7686 | |
2018 | Q1 | A | 16263 | 16263 | |
2018 | Q2 | B | 26192 | 26192 | |
2018 | Q3 | C | 9746 | 9746 | |
2018 | Q4 | D | 19769 | 19769 | |
2019 | Q1 | A | 22468 | 22468 | |
2018 | Q1 | A | 10345 | 10345 | |
2018 | Q2 | B | 20117 | 20117 | |
2018 | Q3 | C | 21256 | 21256 | |
2018 | Q4 | D | 8699 | 8699 | |
2019 | Q1 | A | 14199 | 14199 | |
Total | 247613 | 44353 | 48850 ? |
Here Current Quarter is 2019 Q1 and what is the formula to get Previous year same quarter values (I.e., 2018 Q1)
Sales QTD = TOTALQTD([C&L TOTAL], 'Table data'[Date])
LQTD = CALCULATE(
SUM('Table'[Sales]),
DATEADD('Table'[Date], -1, YEAR))
Thanks in Advance
Solved! Go to Solution.
Hi @BhavyaM,
It seems like you are work with text format year quarter values.
Obviously, time intelligence function not able to work with this scenario. I'd like to suggest you extract the year and query number as a condition to calculate.
Measure formulas:
QTD =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
[Year] = MAX ( 'Table'[Year] )
&& RIGHT ( [Quarter], 1 ) <= RIGHT ( MAX ( 'Table'[Quarter] ), 1 )
)
)
LYQTD =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
[Year] = MAX ( 'Table'[Year] )-1
&& RIGHT ( [Quarter], 1 ) <= RIGHT ( MAX ( 'Table'[Quarter] ), 1 )
)
)
Regards,
Xiaoxin Sheng
@BhavyaM , with date calendar
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
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.
@BhavyaM , with date calendar
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
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.
Thanks for your help!
Yes this is giving me proper results but when i search with year filter then i am not getting proper result when i calculate QOQ %
Hi @BhavyaM,
It seems like you are work with text format year quarter values.
Obviously, time intelligence function not able to work with this scenario. I'd like to suggest you extract the year and query number as a condition to calculate.
Measure formulas:
QTD =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
[Year] = MAX ( 'Table'[Year] )
&& RIGHT ( [Quarter], 1 ) <= RIGHT ( MAX ( 'Table'[Quarter] ), 1 )
)
)
LYQTD =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
[Year] = MAX ( 'Table'[Year] )-1
&& RIGHT ( [Quarter], 1 ) <= RIGHT ( MAX ( 'Table'[Quarter] ), 1 )
)
)
Regards,
Xiaoxin Sheng
Hi Xiaoxin Sheng,
Thanks for the solution.
I am getting data correctly but when i filter with Year i am not getting exact results. Please find the screenshots.
Regards,
Bhavya.
HI @BhavyaM,
It sounds like you directly apply the filter on your table fields, right?
If this is a case, it will filter other year records. my formula can find out previous value but can't calculate on these ranges. (they are been filtered)
For this scenario, I'd like to suggest you use an unconnected table(not has relationshpi to current table) as source slicer to select specific values. Then you can keep current table filters and use selected value as counting to calculated.
QTD =
VAR selected =
MAX ( selector[Year] )
RETURN
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
[Year] = selected
&& RIGHT ( [Quarter], 1 ) <= RIGHT ( MAX ( 'Table'[Quarter] ), 1 )
)
)
LYQTD =
VAR selected =
MAX ( selector[Year] )
RETURN
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
[Year] = selected - 1
&& RIGHT ( [Quarter], 1 ) <= RIGHT ( MAX ( 'Table'[Quarter] ), 1 )
)
)
Notice: selector is an unconnected table with year fields value extract from current table.
Regards,
Xiaoxin Sheng
Hi,
To be able to use the Date and Time Intelligence functions, you must either have a genuine date column (which you do not have) or a Year column and a Month column. I see you have a Year column so please please share the month column as well.
Typically you need a separate Date table when using "time intelligence" functions. 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...
I have date column in my table
Please help me what will be my formula to get previous year same quarter values it's very urgent Requirement
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
105 | |
105 | |
88 | |
61 |
User | Count |
---|---|
165 | |
133 | |
132 | |
95 | |
86 |