How can I have an industry standard date parameter in Power BI?
I am try to create a date parameter like can in Qlik Sense, Rshiny, Tableau with the following properties: Its a true date parameter that treats date as a series instead of a list. It can have a default date. It can have a min and max range. It able have a ""dynamic"" default value set by a calculation from a field. In my most recent case, I would want that field to the refresh date that the data and semantic model was last refreshed Is fixed at load or refresh P.S. This is kind of a industry standard concept in Business Intellegence at this point.
Code to Find Fiscal Week of a Quarter (445 Calendaring)
Hello all, I am currently trying to make graphs where I am able to drill down from Fiscal Year -> Fiscal Quarter -> Fiscal Month -> Fiscal Week; however, I am having issues figuring out code that can find the given week of a Quarter (1-13 using 445 calendaring) from a given date. The fiscal year I am working with is from May 1st - Apr 30th, so Q1 would be May, June and July so on and so forth. In the below image, I found the Fiscal Year, Quarter and Month with functions on Excel, but I couldn't figure out the code for week so hopefully someone can do it via DAX. An example of an expected result using the below table would be 1/2/2018 = Week 5 and 2/6/2018 = Week 10 Any help/guidance would be much appreciated. Please let me know if you need any clarification. ThanksPython to Dax help
Hello all, I am currently trying to convert this below code into something that I can paste into DAX and create a new column that reads a date column and returns the fiscal week. To clarify, my Fiscal Year starts the week containing May 1st (Sun -Sat) and operates on 445 calendaring. If anyone could guide me on how to convert it that would be a huge help, thanks. Thanks a tonAvoid adapting a measure in a table, and IF() returning too many rows
Hi everyone, Here's a WeTransfer for the PBI in case you want to help me: https://we.tl/t-sfdijb0Q4Y I have 3 tables, linked together in the follwoing way: So far, for a selected Category (selected with a slicer on the category's name), I display all the information that I have in the Table Feuil2 and the sum of Value from Feuil3. In addition, I computed a weighted average of the Value for each attribute in the selected category, weighted by their sales. For this, I used the following DAX formula: average_value_weighted_by_sales = DIVIDE( SUMX(Feuil3, Feuil3[Value] * CALCULATE(SUM(Feuil2[AttributeSales])) ), CALCULATE(SUM(Feuil2[AttributeSales])) ) My Power BI looks like that: What I would like to do it add a column to the Table visual that would take the value "OK" if the sum of an attribute's value is smaller than the displayed average_value_weighted_by_sales, or "KO" if the sum is greater. In my example, that'd mean that I'd have "KO" for the first row (since 1.94383 > 1.81081), "OK" for the second row (since 1.51867 <= 1.81081), and "KO" for the last row (since 2.2050 > 1.81081). Of course, I would need this to be dynamic when we select a new category. While doing to do this, I have faced two problems: The measure that I created adapts to the context (I guess that's the name?), meaning that if for an attribute I ask how its value compares to the measure, it's always equal since the measure wil be computed based on the attribute only. We can see that by placing the measure in the visual and we will see that the columns Value and average_value_weighted_by_sales will be equal. Th function IF (which I think is the next step) returns all the rows and not only the ones I filtered with the slicer. Thank you for you help!Sharing: How to create test data using DAX!
Hi, In this article, I will show how to generate sample data and how to add this to your data model. The result is a sales table and related dimensions: This is the code to generate the sales table: Sales = VAR _tbl = SELECTCOLUMNS ( CROSSJOIN ( ROW ( "test", DATE ( 2020, 10, 3 ) ), GENERATESERIES ( 1, 60, 1 ) ), "StartDate", [test], "Increment", [Value] ) VAR _dates = ADDCOLUMNS ( _tbl, "NewDate", [StartDate] + [Increment] ) RETURN GENERATE ( SELECTCOLUMNS ( { "Vendor 1", "Vendor 2", "Vendor 3", "Vendor 4", "Vendor 5" }, "Vendor", [Value] ), SELECTCOLUMNS ( ADDCOLUMNS ( CROSSJOIN ( _dates, GENERATESERIES ( 1, 10, 1 ) ), "open_time", [NewDate] + NORM.INV ( RAND (), 0.5 + ( [Increment] / 100 ), 0.08 ), "PRODUCT", CONVERT ( UNICHAR ( RANDBETWEEN ( 65, 90 ) ), STRING ) & CONVERT ( UNICHAR ( RANDBETWEEN ( 65, 90 ) ), STRING ) & CONVERT ( UNICHAR ( RANDBETWEEN ( 65, 90 ) ), STRING ), "sales_amount", NORM.INV ( RAND (), [Value], [Value] / 10 ) ), "sales_date_time", [open_time], "PRODUCT", [PRODUCT], "sales_amount", [sales_amount] ) ) You can add the above code by navigating to the ribbon in Power BI, select 'Modelling' to then select 'New Table'. Paste the above code in the editor. Explanation In the next part, I will break down the working of the code. First, we generate a series of dates: We started with a default date and added an incremental column, that column sets the number of days that we will add to the default data. Next is adding vendors: SELECTCOLUMNS ( { "Vendor 1", "Vendor 2", "Vendor 3", "Vendor 4", "Vendor 5" }, "Vendor", [Value] For each vendor, all the dates (generated above) are added and a timestamp is added: SELECTCOLUMNS ( ADDCOLUMNS ( CROSSJOIN ( _dates, GENERATESERIES ( 1, 10, 1 ) ), "open_time", [NewDate] + NORM.INV ( RAND (), 0.5 + ( [Increment] / 100 ), 0.08 ), A product is added by randomly selected characters from the UNICHAR array: "PRODUCT", CONVERT ( UNICHAR ( RANDBETWEEN ( 65, 90 ) ), STRING ) & CONVERT ( UNICHAR ( RANDBETWEEN ( 65, 90 ) ), STRING ) & CONVERT ( UNICHAR ( RANDBETWEEN ( 65, 90 ) ), STRING ), A sales amount is added: "sales_amount", NORM.INV ( RAND (), [Value], [Value] / 10 ) Finally, the required columns are added: "sales_date_time", [open_time], "PRODUCT", [PRODUCT], "sales_amount", [sales_amount] ) With the above steps, we have created the fact table. With that table in place we can add the dimension tables by simply adding a table with DAX like below: dimProducts = VALUES(Sales[PRODUCT]) As a final step, we add the relationships in the data model. That is hopefully on familiar grounds. You can have fun with generating data and creating art from it as well 🙂 , see below: The above image is from a response that I wrote when helping out somebody with a DAX challenge. Hope that you find it useful. An example is attached. Kind regards, Steve.
How to - Conditional Formatting - Percentage
Hi Everyone, First time posting so go easy on me. I am looking for a way to achieve the below result that Columns A+B+C+D=E But if Column E is greater than 80% of Column F, it will be formatted Red. Stored Red Apples Stored Blue Apples Shipped Red Apples Shipped Blue Apples Total Apples Limit 1 1 1 4 7 10 1 1 1 5 8 10 Hope this makes sense.....
