Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
So I have some logic in different places, and I'm trying to bring it into one case statement in SQL. This is a little new to me, but I thought this may be optimal versus having a bunch of calculated columns.
Below is an example of my current query:
The "problem", is I then need to use these "flags" that I've defined below and I create a column in Power BI that returns the MAX d.cal_date from edw.d_dates where the flag is a "Y".
This is what I used for my column calculation:
Last Inventory Date = RETURN(CALCULATE(MAXX(Inventory, Inventory[Cal_date]), filter ('D_Dates', 'D_Dates'[Week_end_flag]="Y")).
Again, I'm looking for a way to combine these together. I don't necessarily NEED the Week_End_Flag as a column. It's the last date that I really care about.
To visualize it, column D is what I'm going for here:
Quantity On Hand | Cal_Date | Flag | Last Inventory Date |
350 | 1/18/2021 | N | 1/21/2021 |
335 | 1/19/2021 | N | 1/21/2021 |
320 | 1/20/2021 | N | 1/21/2021 |
400 | 1/21/2021 | Y | 1/21/2021 |
380 | 1/22/2021 | N | 1/21/2021 |
370 | 1/23/2021 | N | 1/21/2021 |
TIA!
Solved! Go to Solution.
Thank you so much for your response Tom!
So I am using an import to pull in the data from an Oracle Database.
You could consider the data is organized into two data imports.
1. One is an inventory table that contains quantity, customer name, item number, and store number.
2. I also have a date table that I am pulling in. Where it gets a little complex is that I am looking at data for 7 different customers who all have their own calendar year/weeks. This is called out in the date table, for example, there is a column that states "Walmart Fiscal Year, Walmart Fiscal Week".
So initially, I created those flags on my dates table (one for each customer) to flag their end of week with a "Y". I then created a column in Power BI on the inventory table, to return the max date that has a flag (and has inventory data). So this essentially would give me the max date (less than todays date) that has a "Y" flag.
In the end, I'm after this question: "What is the current inventory for this customer?" So, to get here, I then created a calculated column that sums the quantity on hand for that last inventory date.
Again, all of this works fine, but I just feel like it's so many steps, and maybe there is a way to eliminate some of them to make my report run a little more efficiently.
Future, I would also like to be able to see inventory for the same customer fiscal week for last year. I'm not quite ready to tackle this one yet though. 😉
Thank you so much for your response Tom!
So I am using an import to pull in the data from an Oracle Database.
You could consider the data is organized into two data imports.
1. One is an inventory table that contains quantity, customer name, item number, and store number.
2. I also have a date table that I am pulling in. Where it gets a little complex is that I am looking at data for 7 different customers who all have their own calendar year/weeks. This is called out in the date table, for example, there is a column that states "Walmart Fiscal Year, Walmart Fiscal Week".
So initially, I created those flags on my dates table (one for each customer) to flag their end of week with a "Y". I then created a column in Power BI on the inventory table, to return the max date that has a flag (and has inventory data). So this essentially would give me the max date (less than todays date) that has a "Y" flag.
In the end, I'm after this question: "What is the current inventory for this customer?" So, to get here, I then created a calculated column that sums the quantity on hand for that last inventory date.
Again, all of this works fine, but I just feel like it's so many steps, and maybe there is a way to eliminate some of them to make my report run a little more efficiently.
Future, I would also like to be able to see inventory for the same customer fiscal week for last year. I'm not quite ready to tackle this one yet though. 😉
Hey @Anonymous ,
I have to admit that I do not understand what you want to create, can you please clarify this.
My question(s) is about the nature of your Power BI dataset, are you connecting to the underlying source data using direct query or are going to import data from the source data.
I do not understand why you want/have to brig the logic to the SQL statement
Assuming you are going to import the data, then you can create a dedicated calendar table inside your Power BI data model.
Inside this table you can create columns that contain the values for start date and end date of a week, this is described here: Solved: Re: Week commencing in DAX - Microsoft Power BI Community
After a relationship is established between your calendar table and your fact table you can easily create the column Week_End_Flag by using a DAX like this
Week_End_Flag = RELATED( 'Calendar'[EoWDate].
To be honest at the current moment, I can't say if the DAX creates a calculated column or is used to compute a virtual column inside a more complex measure.
This is because I do not understand the nature/meaning of Last the /relationship of the tables used for the final statement.
Maybe you want to clarify this.
Maybe this article Design a data model in Power BI - Learn | Microsoft Docs will help to finally decide if you bring down the DAX statement to the SQL statement or the other way round.
Regards,
Tom
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |