Dax Script
hello all, i'm wondering if someone can help m, being trying to make this measure since the last 1 week now my client asked me to produce a dashboard displaying only the last message received here is the scenario. its a reservation system, users have id call PDL and eache PDL can have one or multiple message when booking an appointement. types of message: - Prise de rdv en succès -proposition possible pas de possibilité de rdv etc.. now the mesure that i need to create is the count of all last message per max(hour). i made the following script test_mesure:=CALCULATE( DISTINCTCOUNT('ANALYSE PRV MESURES'[PDL]); LASTNONBLANK('ANALYSE PRV MESURES'[DATETIME];'ANALYSE PRV MESURES'[DATETIME]) ) and this is not working. someone has an idea on how to make it ?DAX - calculate invoices
Hello, I have a formula that filters customers who NEVER bought, but BUY in the last two months. Now I need the quantity of these invoices, each one has a different document number. I tried to change it within the formula to "Nº Documento" (which is the invoice number), without success. Below is the formula used and the value in red that I need to find: Qtd. Faturamento Bimestral = VAR AllOldCustomers = CALCULATETABLE ( VALUES ( SBOPRODMS[Nº Documento] ), SBOPRODMS, SBOPRODMS[Data NF] < TODAY () - 60, SBOPRODMS[Documento] = "Nota fiscal de saída" ) VAR AllNewCustomers = CALCULATETABLE ( VALUES ( SBOPRODMS[Nº Documento] ), SBOPRODMS[Data NF] < TODAY () , SBOPRODMS[Documento] = "Nota fiscal de saída" ) RETURN COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) )
Average calculation showing incorrect values
Hello everyone. I have a report that consolidates Metric information from several vendors. There is a common columns called Metric where I have all Metrics scores Metric_ID Score Users Metric 1 70% User1 Metric 2 60% User1 Metric 1 68% User2 Metric 3 87% User 1 in the report I added the collumn Score so the idea was to see with no filter: Metric Score Metric 1 69% Metric 2 60% Metric 3 87% and then if I filter by user to see the metri to that user like: USer 2 Score 68%. Before I set any filter using the slicer when I as supposed to see all metrics score averaged by Metric type i am geting completelly non sense scores. in the table I added the Score column and set as "Average". Should I create something different to do all calculations? thank you in advance FabioThe True/False expression does not specify a column. Each True/False expressions used as a table fil
Hi everyone, I am looking for a solution to the error I'm facing below. I have tried created a New Measure with the following Formula. Can someone point me where I'm making a mistake please? I have tried using Median inside the variable below but unfortunately not able to, is this what is causing this error? My aim is to have one Calculated column with the Trading Gap instead of having 8 columns. Any help/guidance is appreciated. 1 Trading Gap $ = VAR CurrentSales = sum('Store data'[Sales MAT]) VAR VarietalProducts = calculate(counta('Store data'[PRODUCT_NAME]), ALLEXCEPT('Store data','Store data'[STORE_CODE], 'Store data'[Varietal]), CurrentSales > 0) VAR VarietalProductsSales = CALCULATE(sum('Store data'[Sales MAT]), ALLEXCEPT('Store data','Store data'[STORE_CODE], 'Store data'[Varietal])) VAR PartofVarietalProducts = divide(1,VarietalProducts) VAR PartofVarietalProductsSales = divide(CurrentSales,VarietalProductsSales) VAR LocalIndex = DIVIDE(PartofVarietalProductsSales,PartofVarietalProducts,0) VAR MedianIndex = CALCULATE(MEDIAN('Store data'[Local Index]), ALLEXCEPT('Store data','Store data'[PRODUCT_NAME]), CurrentSales > 0 ) VAR PotentialSales = MedianIndex * PartofVarietalProducts * VarietalProductsSales RETURN CALCULATE ((PotentialSales-CurrentSales), CurrentSales > 0 ) ThanksGetting count of records between two dates
I have a fact table that displays various states of a piece of equipment. I want to find the # of records between two "states" in a table. So you see I have a changeover at 1/11/21 at 9:53:26 and another one at 1/11/21 at 11:17:18. There are 12 records in between these two states: I am creating some measures to get this count. But I cannot see to get my DAX to pick up the proper number, as it keeps displaying the total number of records in the table for this piece of equipment (L4 Filler). My measures are as follows: This State Date/Time = VAR ThisCODate = SELECTEDVALUE(vw_fact_Utilization[util_start_date]) Return ThisCODate Next State Date/Time = VAR CODate = SELECTEDVALUE(vw_fact_Utilization[util_start_date]) VAR COAfterDate = CALCULATE(MIN(vw_fact_Utilization[util_start_date]), FILTER(ALL(vw_fact_Utilization[util_start_date]), vw_fact_Utilization[util_start_date] > CODate)) Return COAfterDate Total per Changeover = VAR CODate = SELECTEDVALUE(vw_fact_Utilization[util_start_date]) VAR COAfterDate = CALCULATE(MIN(vw_fact_Utilization[util_start_date]), FILTER(ALL(vw_fact_Utilization[util_start_date]), vw_fact_Utilization[util_start_date] > CODate)) VAR row_count = CALCULATE(COUNTROWS(ALL(vw_fact_Utilization[util_start_date])), FILTER(vw_fact_Utilization, vw_fact_Utilization[util_start_date] > [This State Date/Time] && vw_fact_Utilization[util_start_date] < [Next State Date/Time])) Return IF(ISBLANK(row_count), 0, row_count) My "Total per Changeover" column is essentially trying to count rows between the two dates, but using the entire table because the rows inbetween the "Changeover" state are what I want to count (the lines with Producing, Faulted, Starved, etc.). But I also need to filter it by the equipment (L4 Filler). So there are 12 rows between 9:53 and 11:17 changeover records. It doesn't seem to using the FILTER I have in the row_count variable that only picks up the date range using greater and less than. What am I doing wrong! Help! Thanks.
Calculating average value for a day of a month comparing to other value
Hello everyone, I want to calcutalte average value for a day of a month comparing to other value with Measure: Measure1 = VAR tm = CALCULATE(SUM('table'[data1]), FILTER(ALL('calendar'), 'calendar'[date] <= 'calendar'[date])) return DIVIDE('tm', 'table'[NumberofDays], 0) Measure2 = VAR tm = CALCULATE(SUM('table'[data2]), FILTER(ALL('calendar'), 'calendar'[date] <= 'calendar'[date])) return DIVIDE('tm', 'table'[NumberofDays], 0) MeasureResult = DIVIDE('table'[Mesaure1], 'table'[Measure2], 0) But MeasureResult returns the all same value on Table visual. How to get value row by row? Thanks in advance, VladiCurrent Month Compare To Previous Month based on the given data
Hi, I would like to seek for your help. I have this data to compare the sales from Current month to Previous month. See my figures below: From there, I compare the April 2019 to March 2019. And I would like to show on my report this way (see image below): The arrow will depends if the amount INCREASE or DECREASE TO/FROM The amount is the April Sales Amount What DAX or Formula should I use? TIA for the Help. I appreciate it. Stay safe guys! Thanks and regards, ECHO
