Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I've been stuck in this task for a day. I trying to display the "total sales" for a quarter when you select a month. Ex: If you select May, it will display the total sales for April, May, and June (all these three months together, no only May as you can see in the example below). Thank you, I'd appreciate ideas
Solved! Go to Solution.
@Gabe_07 , if you have date try datesqtd
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Power BI — QTD Questions — Time Intelligence 2–5
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Hi @Gabe_07 .
If what you are showing is the actual data and you have year and period/month, why don't you just create a separate table for Month/Quarter... I added in Semi Annual though it is rarely used in Finance.
Month | Quarter | Semi Annual |
January | Q01 | Semi01 |
February | Q01 | Semi01 |
March | Q01 | Semi01 |
April | Q02 | Semi01 |
May | Q02 | Semi01 |
June | Q02 | Semi01 |
July | Q03 | Semi02 |
August | Q03 | Semi02 |
September | Q03 | Semi02 |
October | Q04 | Semi02 |
November | Q04 | Semi02 |
December | Semi02 |
Create a Relationship/Join between this table and your main datasource table... The caveat is that you should have something for unassigned if there are null values in your datasource table.
Hope that helps.
Lou
@Gabe_07
Try create the following measure:
Measure = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),QUARTER([Date])=SELECTEDVALUE('Table'[Date].[QuarterNo])))
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Gabe_07
Try create the following measure:
Measure = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),QUARTER([Date])=SELECTEDVALUE('Table'[Date].[QuarterNo])))
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Gabe_07 .
If what you are showing is the actual data and you have year and period/month, why don't you just create a separate table for Month/Quarter... I added in Semi Annual though it is rarely used in Finance.
Month | Quarter | Semi Annual |
January | Q01 | Semi01 |
February | Q01 | Semi01 |
March | Q01 | Semi01 |
April | Q02 | Semi01 |
May | Q02 | Semi01 |
June | Q02 | Semi01 |
July | Q03 | Semi02 |
August | Q03 | Semi02 |
September | Q03 | Semi02 |
October | Q04 | Semi02 |
November | Q04 | Semi02 |
December | Semi02 |
Create a Relationship/Join between this table and your main datasource table... The caveat is that you should have something for unassigned if there are null values in your datasource table.
Hope that helps.
Lou
@Gabe_07 , if you have date try datesqtd
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Power BI — QTD Questions — Time Intelligence 2–5
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
@amitchandak Hi, the DAX
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
I've already used it, it keeps changing me the Totals Sales when I select a month. I'm trying to set three months calculation when you select a month. Sorry If it doesn't make sense, these two images below have the same year but different months (also the are in the Q2). These should have the same total amount because they belong to the Q2.
@Gabe_07 , My post got saved half. First of all, do you have date. Then you can use datesqtd. And month and year should be in date table.
1.Creating Financial Calendar -of your choice
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
Power BI — QTD Questions — with or without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
if you do not have date use the Rank approch metioned in QTD questions
@Gabe_07 -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.
But, in general you will need to use an ALL and/or ALLEXCEPT statement in your measure to break out of the current filter context. You can then filter your dates back down to what you want. 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...
@Greg_Deckler I appreciate the help, I'll try to build a DAX with those clauses (ALL and/or ALLEXCEPT)
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |