Ayuda para crear medida filtro propagado
Hola, saludos desde República Dominicana! Tengo una consulta de cómo crear una medida para obtener en mi modelo la cantidad de clientes del mes máximo propagado a todos los meses. Aquí dejo la data de ejemplo, en donde [Clientes] es una medida que suma los clientes, y la medida [Clientes Mes Máximo] es lo que debería tener como resultado: Año Mes Clase [Clientes] [Clientes Mes Máximo] 2024 7 A 299,670 520,870 2024 7 B 619,913 494,458 2024 7 C 231,127 147,026 2024 7 D 72,286 72,436 2024 8 A 248,411 520,870 2024 8 B 688,156 494,458 2024 8 C 211,726 147,026 2024 8 D 86,447 72,436 2024 9 A 352,726 520,870 2024 9 B 660,621 494,458 2024 9 C 155,889 147,026 2024 9 D 62,539 72,436 2024 10 A 520,870 520,870 2024 10 B 494,458 494,458 2024 10 C 147,026 147,026 2024 10 D 72,436 72,436 En este ejemplo tengo una matriz que selecciona varios meses y me gustaría una medida que traiga los resultados del último mes del filtro externo. Hice una medida de prueba pero el resultado no propaga la clase: Test Clients = CALCULATE( SUMX( VALUES(dim_Calendario[Numero Mes]), [Clientes] ),ALLSELECTED(), dim_Calendario[Numero Mes] = MONTH(MAX(dim_Calendario[Date])) ) Aquí el resultado. Este es mi modelo: ¿Estoy obviando algún otro filtro? Muchas gracias de antemano por el soporte!
Calculated Measure Not showing up
I am trying to get a Prior Week measure to calculate from a Current Week measure. Other measures based off of the Current Week measure work but the measures based off of the Prior Week do not. Can anyone help me with this? I think it is an issue with the Prior week output value type but cant seem to figure it out. Current Week measure Calc: This works in all Current Week measures except Prior Week Prior Week measure Calc and Outputs: Do you know why or if there is a formula that call pull prior week from a long list of weeks with duplicates? Thanks Connor
DAX measure for calculating Employee exits
I have the following 4 tables (there are more tables and columns, but these are the ones that I am concerned with for my calculation) and their respective columns: 1) Date_table (Date, Year, Quarter, Month, Day) 2) Employee_table (Emp_Id, StartDate, EndDate, Termination, Country, Wok_Id) 3) PositWorker_table (Wok_Id, JobType, Department) 4) Worker_table (Wok_Id, Gender, EmploymentCategory) The following relationships exist between these tables: 1) Employee_table(EndDate) links with Date_table(Date) [Single direction (cross filter), Many:1 relationship] 2) Worker_table(Wok_Id) links with PositWorker_table(Wok_Id) [Single direction (cross filter), Many:1 relationship] 3) Employee_table(Wok_Id) links with Worker_table(Wok_Id) [Single direction (cross filter), Many:1 relationship] 4) Employee_table(Wok_Id) and PositWorker_table(Wok_Id) [Single direction (cross filter), Many:1 relationship] The following filters are being used on a page level: 1) Employee_table(Termination) is not equal to "Transfer" (there are 5 other options) 2) Worker_table(EmploymentCategory) is not equal to "Intern" or "Working Student" (there are 4 other options) 3) PositWorker_table(JobType) is not equal to "Intern", "Student" or "Blank" (there are 8 other options) 4) PositWorker_table(Department) is not equal to AAD (there are 30 more options) I am using the following measure: Exits = VAR exits = CALCULATE ( COUNT ( Employee_table[Emp_Id] ), USERELATIONSHIP ( Employee_table[EndDate], Date_table[Date] ) , CROSSFILTER(Worker_table[Wok_Id],PositWorker_table[Wok_Id], Both) ) RETURN IF ( ISBLANK ( exits ), 0 , exits ) The results being generated are slightly higher than what should be coming. What changes need to be made in the measure or relationships of the data model so that I get the right answer?
Running total up to certain month
Hi, Currently I have a column labled date that has all the dates from 2010 to 2030. I have another columns with lengths for each of these dates. I would like to get a running count of the length for up to the dates in 2014, and then every year after that up to the current year we are in I want to add 10. This will be a line that gradually goes up in my chart. Right now I have, CALCULATE(SUM(table[goal])/5280, Filter(AllSelected(table),YEAR(table[Date]) <= 2014, table[Date before prev month] <> BLANK() ) + (YEAR(TODAY()) - 2014) * 11 but this does not give me a line that gradually goes up. Can someone point me in the right direction?
How to Count records and partition by a column
Hi Community, I am trying to do a count partitioned by date and area as shown below: Currently using a formula to get the count selected: CountSelected = CALCULATE(Count(table[rows]), ALLSELECTED(table)) I have tried using ALLEXCEPT function which somewhat works but that ignores my slicer selection unless im using it wrong. Thanks in advance
Help Calculating Available Quantity Correctly
Got a data related issue that is being fixed in the background but could take a while, so I am looking for a DAX Band-Aid to just let me work with what I've got for right now. Our company database stores an "Available Quantity" field to reflect how much we have of a particular item that is available for purchase (either in a warehouse or in transit to us). However, this field does not contain any 0's and instead only has blank rows for the Quantity column. There is also an "Available Quantity %" field that SHOULD be calulcating the number of items in a category for which there is at least 1 unit available, and then dividing this by the total number of distinct items in that category. For instance, we sell 75 different items in the "Desk" Category, and if 19 of these desks had at least 1 unit available for purchase, the "Available Quantity %" would be 25%. This is clearly not the case though, and our data team will hopefully resolve why they all say 100% soon. However, for now, I was hoping to use DAX to get the correct % that I need, and I have created a measure that simply gives the Available Quantity, for each item, but replaces blank values with 0 (the 4th column in the table on the right, "MSR - All Available Quantity"). I am TRYING to get a sum for either all the items in a category that HAVE availabilty (> 0) or do NOT have it (blank or = 0), as I only need one or the other to be able to divide this by the total number of items in the category and get an accurate %. I have tried COUNTROWS(), ISBLANK(), SUMX(), and anything I can think of but I cannot get a good measure created. I even made a measure to flag items that gives 1 if there is a unit for purchase and 0 otherwise, for every single item we sell, and was hoping to filter by category and take a sum, but this has proven difficult... Please help!!!Dax USERELATIONSHIP not working
I put together as a simple example of an issue that I'm having with the UseRelationship dax measure not working. The problem is I have a date table where the date column is the unique key and we have a few more columns off to the side that map "Date" to its respective prior year date so that we can use the “UseRelationship” calculation to look at prior date values for a given date selected. In our dates table there are 2 additional columns that look back one year from the current date. One day called [Minus1Year] and is just subtracting one year from the date therefore there is a one to one relationship between date and minus one year. However, some dates are tagged to the same “date one year ago” value which is a separate column. For example, leap year. In the example I provide you will see that there are a few date rows that are mapped to the same “date one year ago” value and for the “minus one year” those values are unique and different. See highlighted example below. What is interesting is that if I build a dax measure that leverages the "date one year ago" relationship the relationship does not work because it is a many-Many relationship with the detail sales table, however, if there is a one to many relationship then the UseRelationship calculation works. As you can see in the example below I have a column for dates and I have a column for date one year ago which does have repeating values for some dates hence January 4th 2019 shares the same date one year ago with January 5th and January 6th of 2019. The revenue column is simply is a sum of the revenue for the given date. No issue here…. Revenue = SUM(Sales[Amount]) The "Date one year ago" is calculating the revenue amount but using the relationship between date one year ago from the dates table connecting to the transaction date column of the sales table. This calculation is not working properly and is only returning the revenue value. So you can see date one year ago revenue is equal to the revenue column. The minus one year revenue column seems to be calculating properly since that is a unique field from the dates table. How do I fix the date one year ago revenue measure so that given a specific date it uses the respective date one year ago and returns the revenue value from that time similar to how minus one revenue works today. What I would expect to see is that on the values for January 4th 5th and 6th of 2019 they share the same revenue values since the “date one year ago” is the same for all three. Why isn’t this working and how can I get this to work? Below are a few screenshots to help. Here is the model. As you can see all fields from the dates table tie to the transaction date column of the sales fact table. Here is a view of the dates table. As you can see for some of the dates they share the same “date one year ago” value. This is done by design because some dates need to share the same “date one year ago” to account for leap year or other reporting dates. Here is a quick crosstab view showing by date and their respective “date one year ago” and “minus one year” columns. The values in the “DateOneYear – Revenue” are not correct and are simply equaling the Revenue column. As an example, below you can see for January 4th 5th and 6th of 2019, the "date one year ago" was January 4th 2018….so I would expect to see for each of these three rows in the date one year ago revenue column the value of $13.2 million... unfortunately it's simply equaling the revenue. Why is this not working while the “Minus1Year – Revenue” column is working and how do I fix this? Here are the formulas for each of the 3 measures above. Hoping this is an easy answer…. DateOneYearAgo - Revenue = CALCULATE([Revenue],USERELATIONSHIP(Dates[DateOneYearAgo],Sales[TnxDate])) Minus1Year - Revenue = CALCULATE([Revenue],USERELATIONSHIP(Dates[Minus1Year],Sales[TnxDate])) Revenue = SUM(Sales[Amount]) Here are the expected results which I am not able to see... Thougths on what I am doing wrong and what is the fix for the "DateOneYearAgo - Revenue" measure?
