Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gabe_07
Helper I
Helper I

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  EXAMPLE.png

3 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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

View solution in original post

Anonymous
Not applicable

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.

 

 

MonthQuarterSemi Annual
JanuaryQ01Semi01

February

Q01Semi01
MarchQ01Semi01
AprilQ02Semi01
MayQ02Semi01
JuneQ02Semi01
JulyQ03Semi02
AugustQ03Semi02
SeptemberQ03Semi02
OctoberQ04Semi02
NovemberQ04Semi02
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

View solution in original post

V-pazhen-msft
Community Support
Community Support

@Gabe_07 
Try create the following measure: 

 

Measure = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),QUARTER([Date])=SELECTEDVALUE('Table'[Date].[QuarterNo])))

 

sum qtr.JPG

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.

View solution in original post

8 REPLIES 8
V-pazhen-msft
Community Support
Community Support

@Gabe_07 
Try create the following measure: 

 

Measure = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),QUARTER([Date])=SELECTEDVALUE('Table'[Date].[QuarterNo])))

 

sum qtr.JPG

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.

@V-pazhen-msft  Thank you for the help. I got confused how to formulate the DAX

Anonymous
Not applicable

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.

 

 

MonthQuarterSemi Annual
JanuaryQ01Semi01

February

Q01Semi01
MarchQ01Semi01
AprilQ02Semi01
MayQ02Semi01
JuneQ02Semi01
JulyQ03Semi02
AugustQ03Semi02
SeptemberQ03Semi02
OctoberQ04Semi02
NovemberQ04Semi02
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

amitchandak
Super User
Super User

@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.  

EXAMPLE.pngEXAMPLE2.png

@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

Greg_Deckler
Super User
Super User

@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...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  I appreciate  the help, I'll try to build a DAX with those clauses (ALL and/or ALLEXCEPT)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.