cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HARMKE964
Frequent Visitor

Previous Quarter No Result

All,

 

I want to know my previous quarter dollar ammount shipped.  I am having an issue due to when I calculate previous quarter, it does not show anything.  This is what I came up with thus far:

 

PREVIOUS Q DOLLAR AMMOUNT:=CALCULATE([TOTAL DOLLAR AMMOUNT SHIPPED],PREVIOUSQUARTER(CALENDAR[DATE]))

 

Now, it is worth to mention that I am connecting to SQL Analysis Services database and I created the model.  In my model I have a CALENDAR table and this is where I am pulling "CALENDAR[DATE]" however it is not working.  I have tried to use a date from my fact tables but I get the error of "you are trying to use a column with duplicates" and it wont do anything.  Any suggestions?

 

Thanks,

6 REPLIES 6
mantasceponis
Frequent Visitor

you can use a pattern from here: 

 

https://www.daxpatterns.com/time-patterns/ 

 

It usually helps with time intelligence 🙂 

v-jiascu-msft
Microsoft
Microsoft

Hi @HARMKE964,

 

Please share a dummy sample.

1. Make sure the measure has proper context.

2. What's the formula of [TOTAL DOLLAR AMMOUNT SHIPPED]?

3. What's the connection mode? Import or Direct query.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

By the way, the connection is Direct.

 

Thanks,

Ok, so I got it now, you all need more information (understood).  This is how my model looks like so you all can see the relationships:

 

Relationships.PNG

 

The formula I am using for "TOTAL DOLLAR AMMOUNT SHIP" is based of:

 

Step 1

=CALCULATE(AVERAGEX(RELATEDTABLE('QUERY OEORDER DETAIL'),'QUERY OEORDER DETAIL'[UNIT_PRICE]))  :

I am doing this average so I can bring the price of parts from 'QUERY ORDER DETAIL' in to 'QUERY ORDER SHIPPING'

 

Step 2:

=CALCULATE(SUM([AVG UNIT PRICE DETAIL])*SUM([QTY_SHIPPED])) :

Then I create a colunm that will display the dollar ammount shipped

 

Step 3:

TOTAL DOLLAR AMMOUNT SHIPPED:=SUM([AVG USD SHIPPED])  :

A simple SUM at the bottom of my column created at step two

 

New columns.PNG

 

Step 4:

 

I tried the formula that I already posted at the begining of this post. I hope this helps.

 

Thanks,

 

 

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

HARMKE964
Frequent Visitor

All,

 

I am trying to get my previous quarter dollar ammount shipped.  It is worth to mention that I am working on a data model I created in SQL Server Analysis Srevices.  The model has a CALENDAR table, so I am using the date from this table.  My dax formula thus far is:

 

PREVIOUS Q DOLLAR AMMOUNT:=CALCULATE([TOTAL DOLLAR AMMOUNT SHIPPED],PREVIOUSQUARTER(CALENDAR[DATE]))

 

But this formula is not returning anything.  I triend to use a date column from my fact table but I will get an error stating that I can not use a column with duplicate values.  Any sugestion?

 

Thanks,

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors