Estimated total sales based on the mean
Hello community, I have a longer question. I will try to describe the whole thing as clearly as possible. 1) This is my data I use the database ContosoRetailDW_2019. From this I imported the following 2 tables: Fact Sales DimDate Of course, both tables are linked via a 1-to-N relationship. I only imported sales before 8/1/2009. If I have the total sales per year and month displayed in a matrix, this is the picture I get: 2) This what I want to do I would now like to estimate the total sales for the months of August to December 2009, using the mean value of the total sales for the past few months. This is a bit difficult to describe, so I'll show you what I want to do in Excel: xcel) 3) These are my measures 3.1) The measure Total Sales I start by creating the Total Sales measure: Total Sales = SUM(FactSales[SalesAmount]) 3.2) The measure Total sales one month all years Now I need a measure that adds up the total sales of a month over all years. I will use it as a dividend in my closing calculation. For a better understanding, I'll show you again in Excel what I want to calculate: Here is the measure Total sales one month all years: Total sales one month all years = CALCULATE( [Total Sales], ALL(DimDate[CalendarYear]) ) When I drag the measure into the matrix I get this picture: 3.3) The measure Number of total sales one month all years (here are the problems!) Now I need a measure that gives me the number of total sales. So that you know what I mean, I'll show you again in Excel what I want to do: I wrote the following measure Number of total sales one month all years: Number of total sales one month all years = COUNTX( ADDCOLUMNS( SUMMARIZE( ALL(DimDate), DimDate[CalendarYear], DimDate[CalendarMonthLabel] ), "Result", [Total Sales] ), [Result] ) The idea is this: I create a table grouped by year and month with SUMMARIZE and then add a result column with ADDCOLUMNS, in which I calculate the total sales. I then use COUNTX to count how many results I have in my summary table. You can see that I get the correct numbers in the values of the matrix. The result column is also correct. However, I cannot explain the values in the result line. Here the total amount (31) comes out everywhere. 3.4) The measure Estimated total sales Anyway, I'll keep going on. I now create the Estimated total sales measure: Estimated total sales = [Total Sales One month All years] / [Number of Total Sales One Month All Years] 3.5) The measure Total sales or estimated sales Now I create the final measure, which outputs either Total sales or Estimated total sales: Total Sales or estimated total sales = IF( ISBLANK([Total Sales]), [Estimated total sales], [Total Sales] ) I drag it into the matrix and of course the overall results are wrong: It should actually look like this: 4) How are you doing right? Thank you so much for reading this really long text! I am of course aware that the problem lies in the measure Number of Total Sales One Month All Years. Unfortunately I can't find the right solution. Can you help me? Thanks in advance!
Dynamic Ageing
Hello, I am actually working on an AR ageing report. My data model is as follows: I have a AR transaction table with the amount, with a Date filed related to a calendar table. So far, I have managed to get the ageing as at today. I have added a calculated column, to calculate the age of the transaction and have used this measure to make it work: Ageing AR Amount = CALCULATE( PostAR[AR Net], FILTER( DISTINCT(PostAR[Ageing]), COUNTROWS(FILTER('Ageing Group', PostAR[Ageing] >= 'Ageing Group'[Min] && PostAR[Ageing] <= 'Ageing Group'[Max] ) ))) Ageing group table and ageing is as follows: So far so good. However, I need to have the calculated column age as dynamic, so that the ageing report is as at a selected date by the user. I have tried adding the ADDCOLUMNS to caluclated the age based on the date selected, but cannot figured it out.Date Table - DAX - CALENDAR() - ADDCOLUMNS() - Quarter Issue
Hi, I create a new Date table with DAX using the CALENDAR() function. Adding new columns, no problem. When I add a new "Quarter" colum into the table my table stops starting from Jan 1st and move to start from Jul 1st. I can't understand why. Any help greatly appreciated. First Variant (correct) Date = VAR MinDate = DATE(2016,1,1) VAR MaxDate = DATE(2021,12,31) RETURN ADDCOLUMNS ( CALENDAR(MinDate, MaxDate), "Calendar Year", "CY " & YEAR ([Date]) ) Result Second Variant (wrong) Date = VAR MinDate = DATE(2016,1,1) VAR MaxDate = DATE(2021,12,31) RETURN ADDCOLUMNS ( CALENDAR(MinDate, MaxDate), "Calendar Year", "CY " & YEAR ([Date]), "Calendar Quarter", "CQ " & QUARTER([Date]) ) Result Where I am wrong? Thanks
DAX semantic error: Function 'INTERSECT' does not support joining columns types
I am currently working at updating Tabular model hosted on-prem on a SASS server version 13.0. The tabular model is version 1200. We have complex logic created in our measures depending of counting rules from parameters selected in disconnected tables. The model was created using snowflake ‘like’ schema: It can be simplified as below: We have several DAX expressions based on the below code: Admission[Number of Clients] := VAR MinDate = MIN ( 'Date'[Full Date] ) VAR MaxDate = MAX ( 'Date'[Full Date] ) VAR AdmissionDate = FILTER ( Admission, SWITCH ( TRUE (), VALUES ( 'Counting Rules'[Counting Rule] ) = "Starts", Admission[Start Date] >= MinDate && Admission[Start Date] <= MaxDate, VALUES ( 'Counting Rules'[Counting Rule] ) = "Ends", Admission[End Date] >= MinDate && Admission[End Date] <= MaxDate, VALUES ( 'Counting Rules'[Counting Rule] ) = "Active", Admission[Start Date] <= MaxDate && Admission[End Date] >= MinDate, 0 ) ) RETURN IF ( COUNTROWS ( VALUES ( 'Counting Rules'[Counting Rule] ) ) = 1, CALCULATE ( DISTINCTCOUNT ( 'Admission'[ClientKey] ), FILTER ( AdmissionDate, IF ( RELATED ( 'Clients'[Date Of Birth] ) <= MAX ( 'Date'[Full Date] ) && ( ISFILTERED ( 'Client Age'[Age Band] ) || ISFILTERED ( 'Client Age'[Age] ) ), IF ( COUNTROWS ( VALUES ( 'Age Counting Rule'[Age Counting Rule] ) ) = 1, INTERSECT ( VALUES ( 'Clients Age'[Age] ), SELECTCOLUMNS ( ADDCOLUMNS ( VALUES ( Admission[ClientKey] ), "@Age", ROUNDDOWN ( DATEDIFF ( CALCULATE ( MAX ( 'Clients'[Date Of Birth] ) ), VAR AdmissionDateFiltered = IF ( VALUES ( 'Age Counting Rule'[Age Counting Rule] ) = "Age Min", CALCULATE ( MIN ( Admission[Start Date] ), AdmissionDate, EARLIER ( Admission[ClientKey] ) = Admission[ClientKey] ), CALCULATE ( MAX ( Admission[End Date] ), AdmissionDate, EARLIER ( Admission[ClientKey] ) = Admission[ClientKey] ) ) RETURN IF ( VALUES ( 'Age Counting Rule'[Age Counting Rule] ) = "Age Min", IF ( AdmissionDateFiltered < MIN ( 'Date'[Full Date] ) && NOT ISBLANK ( AdmissionDateFiltered ), MIN ( 'Date'[Full Date] ), AdmissionDateFiltered ), IF ( AdmissionDateFiltered > MAX ( 'Date'[Full Date] ) && NOT ISBLANK ( AdmissionDateFiltered ), MAX ( 'Date'[Full Date] ), AdmissionDateFiltered ) ), DAY ) / 365.25, 0 ) ), "Age", [@Age] ) ), BLANK () ), TRUE () ) //GOM Status && IF ( ISFILTERED ( 'Guardianship Status'[Guardianship Type] ) || ISFILTERED ( 'Guardianship Status'[Guardianship Type Description] ) || ISFILTERED ( 'Guardianship Status'[Under Guardianship] ), CONTAINS ( VALUES ( 'Guardianship Status'[Guardianship Type] ), 'Guardianship Status'[Guardianship Type], [Child Protection Order Active] ), TRUE () ) ) ), BLANK () ) The age calculation portion is giving us the ability calculate the minimum or maximum age within a reporting period where a client was admitted so we can report on age client age profils. When using Tabular editor, we get an error message "Function 'INTERSECT' does not support joining a column of type Integer with a column of type ." My understanding: It is due to the function Intersect between the disconnected table 'Client Age'[Age] and the calculated column [@Age]. [@Age] doesn't have a type and therefore the error is created. Has anybody encountered this error, or has an idea on how to resolve the problem?
Convert ADDCOLUMNS + SUMMARIZE to SUMMARIZECOLUMNS
Good afternoon I'm attempting some performance tuning with a complex dax calculation and my research has lead me to the use of SUMMARIZECOLUMNS over SUMMARIZE + ADDCOLUMNS. I was able to accurately calculate average daily sales units over a 6 week period (dropping the high and low week of sales. however, when using this calculation it wrecks my performance. My fact table is named "CONFORM_MOVEMENT" it has about 2.5 million records. In the image below you can see the variable values_displayed where I calculate the base table. I'm summarizing the daily sales units and time period by Store, Item. The daily sales units are then used for determining the max and minimum of the 6 week period. Using the additional Time_period_end column I'm able to determine which week is high and low and then calculate the average where it doesn't equal. I attempted to convert this to a SUMMARIZECOLUMNS table however I keep getting the error: SummarizeColumns() and AddMissingItems() may not be used in this context. --See variable values_displayed1 Here is my code: daily_sales_units_normalized = var values_displayed = CALCULATETABLE( ADDCOLUMNS( SUMMARIZE(FILTER(CONFORM_MOVEMENT,CONFORM_MOVEMENT[is_summary] =1 ), 'Calendar'[time_period_end_DateTime],CONFORM_MOVEMENT[Store], CONFORM_MOVEMENT[ItemNumber]), "@Amt", [daily_sales_units], "@time_period_end",'Calendar'[time_period_end_DateTime]), ALLSELECTED()) var values_displayed1 = SUMMARIZECOLUMNS(DELIVER_STORES[Store], DELIVER_ITEMS[ItemNumber], 'Calendar'[time_period_end_DateTime], filter(CONFORM_MOVEMENT,CONFORM_MOVEMENT[is_summary] = 1), "@Amt", [daily_sales_units], "@time_period_end",SELECTEDVALUE('Calendar'[time_period_end_DateTime])) var minVal = minX( FILTER(values_displayed, 'Calendar'[time_period_end_DateTime] > max('Calendar'[time_period_end_DateTime]) - 42 && 'Calendar'[time_period_end_DateTime] <= max('Calendar'[time_period_end_DateTime])), [@Amt]) var minDate = maxx( FILTER(values_displayed, [@Amt] = minVal), [@time_period_end]) var maxVal = maxX( FILTER(values_displayed, 'Calendar'[time_period_end_DateTime] > max('Calendar'[time_period_end_DateTime]) - 42 && 'Calendar'[time_period_end_DateTime] <= max('Calendar'[time_period_end_DateTime])), [@Amt]) var maxDate = maxx( FILTER(values_displayed, [@Amt] = maxVal), [@time_period_end]) return averagex(FILTER(ALL('Calendar'), 'Calendar'[time_period_end_DateTime] <> minDate && 'Calendar'[time_period_end_DateTime] <> maxDate), [daily_sales_units]) Any advice would be greatly appreciated! Thanks Channing
