Forum Discussion
Static QUARTERS calculation
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
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-d842063da839Hi 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.
8 Replies
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-TITHW/m-p/434008Greg_Deckler I appreciate the help, I'll try to build a DAX with those clauses (ALL and/or ALLEXCEPT)
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-d842063da839amitchandak 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-Calendar-1-5-Power/ba-p/1187441Power BI — QTD Questions — with or without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839if you do not have date use the Rank approch metioned in QTD questions
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.Anonymous Thank you for the help. I got confused how to formulate the DAX
