Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
hope you can help me. I got a simple data model with some Sales data, a Calendar and a Dimension table connected to it.
Key to know is the granularity of my fact table: I only got data at Quarter level (no months, no days). The column Year states always the beginning of each Quarter. It looks like the small sample below:
Product type | Year | Quarter | Value (USD) |
A | 01/01/2019 | Q1-19 | 100 |
B | 01/04/2020 | Q2-20 | 67 |
A | 01/10/2022 | Q4-22 | 83 |
What I'm trying to achieve is the outcome on the Prior Q column, that is a reference to the previous Q without going into uber convoluted Dax expressions, which is the only way I got the following result:
To be clear, I have no problems in getting the right sales data within this filter context, I just want the reference to the Quarter and the Year.
What I managed to get is the outcome on last year's quarter (column LY Q) with the following expressions:
1. Last Q =
MAX('Calendar'[Quarter/Year])
2. LY Q =
CALCULATE([Last Q],
SAMEPERIODLASTYEAR('Calendar'[Dates]))
But when it comes to the Prior Q, I'm lost being a beginner in DAX.
Hope I've been clear.
Thanks for your help!
Solved! Go to Solution.
Hi all,
the best solution I could find is to build the following measure:
Hi all,
the best solution I could find is to build the following measure:
Hi,
Create a Calendar Table with a relationship (Many to One and Single) from the year column to the Date column. In the Calendar Table, create these calculated column formulas
Year = year(Calendar[Date])
Month number = month(Calendar[Date])
Quarter = if(Calendar[month number]>=10,"Q4",if(Calendar[month number]>=7,"Q3",if(Calendar[month number]>=4,"Q2","Q1")))
To your visual, drag Year and Quarter from the Calendar Table. Write these measures
Total = sum(Data[Sales])
Total in previous quarter = calculate([Total],previousquarter(Calendar[date]))
Hope this helps.
Hello @Ashish_Mathur
thanks for your reply. Unfortunately, is not what I'm after. I do have a calendar and if anything, as I'm looking at Prior Quarter, I could add a colum into my Calendar with the following:
Quarter Prior = IF('Calendar'[Month number]>=10,"Q3",
IF('Calendar'[Month number]>=7,"Q2",
IF('Calendar'[Month number]>=4,"Q1","Q4")))
As mentioned, however I do not have an issue in calculating sales or % change for prior quarter - I got a measure that works fine. What I need is the text reference to the prior quarter which I need to put into a card's subtitle - see below. It is the second card's Q3-22 text reference I'm struggling with. The %s themeselves are correct.
Thanks
I misunderstood your question. Share the download link of the PBI file.
Hi,
Apologies for the late reply. As it is a file from work, I had to do some edits – I'm sharing the file through OneDrive https://1drv.ms/u/s!AjvfcnbgheIelU3bn91GQ6Hu9VoK?e=Jdktfa
You'll see 2 cards – Card 1 works fine, Card 2 is where I had the issue with the subtitle but I think I sorted it now. I built a measure named Prior Q/Y. It is a bit convoluted but couldn’t think of any better – Seems to be working but any advice to simplify it are welcomed!
However, I’ve discovered there is an additional issue with the callout value of Card 2 for which I don’t know if I should raise this in a separate message.
What I’m trying to achieve is whatever is stated in title/subtitle:
Hope is clear.
Thanks
For this new issue, please start a new thread.
Thanks for your reply Pat.
I've created a table using the Calendar table:
Have you considered adding a Quarters table (like a date table but just one row per quarter) with a quarter index value (e.g., qtrs from today or from start)? That would allow you to easily do -1 type calculations. The table would have two columns (qtr index and the other with your values like Q1-21, etc. for the relationship to your fact table).
Pat