How to calculate ABS value
Hi, I need ur help that I need to calculate the difference between the number of actual sales and forecast sales, but the positive value and negative value shoudn't be offset, so I need to calculate the absolute difference. And here is my situation, please refer to the link: https://drive.google.com/file/d/1-JcZDiK5D7cm9BwCj4h4g7o_u-g1rnlX/view?usp=sharing
How to display the same cumulative value on one month?
Hello, I am trying to display same cumulative value of one month by selecting one month from a filter. I have attached my Pbix file. As illustrated, currently, cumulative value of March 2022 is 35 M. But, when I select only one month from the Date Range slicer, I get different amount (11.4 M): I think I might have to fix the measure for CumSales, and I need your help. CumSales = CALCULATE( SUM(Revenue[Amount]), filter(allselected('Calendar'), 'Calendar'[Date] <=max('Calendar'[Date]) ) ) Thanks.How to reference a value on related table
I am trying to write a measure (DSO_Measure) that I could reference a value (DaysInMonth) on dim table (Calendar) because there are relationships with other two Fact tables (AR_Aging & Transactions) to do a calculation. This is a measure (DSO_Measure) I am trying to write to do a calculation: I tried with as shown on the bottom, but it did not get the right calculation numbers. There is a relationship between two Fact tables (AR_Aging & Transactions) and Calendar (dim) table. Bottom is an image for the dim table (Calendar). I only captured the portion of image for February 2022 value (DaysinMonth), which is 28. How do I express so that it could reference the right month? For this case, it should only point to 28 for February 2022. If it is January or December, it should reference 31 in the Calendar table. If it is November or September, it should reference 30 in the Calendar table, etc.. Thanks.
Create a dax to get count of events occurred in specific time
I have a data like like event_time_utc 12/18/2021 8:50:07 PM 12/18/2021 8:51:47 PM 12/18/2021 8:53:10 PM 12/18/2021 8:53:30 PM 12/18/2021 8:54:10 PM 12/18/2021 8:56:47 PM I am trying to create metrics to display No. of events that occurred in 1.5 min. So for example, I have a Bus that is GPS enabled and I am getting one entry in my table whenever there is a break applied in Bus or Bus took left or right direction means any movement. If you see data, the bus starts on 12/18/2021 at 8:50:07 PM, so let's take event count as 1, and we keep comparing the event time interval with the next event and will see If the next event is within 1.5 min or more than that. As per data our second event was triggered at 12/18/2021 8:51:47 PM and If we see the difference it is 1.66 which is greater than 1.5, so now our event count will become 2, similarly see the next event is 12/18/2021 8:53:10 PM and if we see the difference it is 2.61 so still the count will become 3, now see the next event 12/18/2021 8:53:30 PM and if we see the difference it is 0.33 which is less than 1.5 so the count will be 3 only. Now let's see the next event 12/18/2021 8:54:10 PM, If we see the difference it is 1.33 which is less than 1.5 so the count will be still 3, next event 12/18/2021 8:56:47 PM, If we see the difference it is 2.61 which is greater than 1.5 so our count will increase to 4 and logic continues like that only in short, If we are taking the difference between two events and If the difference between consecutive events is less than 1.5 Min then we will consider all those events as one bucket and once we see the difference between events is greater than 1.5 then the count will increase by 1 and in this way we keep calculating the count of events. So, In the above example, I am expecting my value as 4 that I want to show in my card visual. At high level: event_time_utc Eventdiff Cnt1.5 Min 12/18/2021 8:50:07 PM 0 1 (start) 12/18/2021 8:51:47 PM 1.66 2 12/18/2021 8:53:10 PM 2.61 3 12/18/2021 8:53:30 PM 0.33 3 12/18/2021 8:54:10 PM 1.33 3 12/18/2021 8:56:47 PM 2.61 4Apply related value of Processing Date (month)
I am sharing Pbix file here (Google drive) for you to take a look. Basically, I need to fix this DAX code or do something to apply value that is related to different Processing Date. I have three processing dates (April, May and June 2021). This is DAX code that I had originally usingTOPN function, but I cannot apply this any longer, but retrieve the value based on Processind Date. So, I need a guidance to modify this code. Currently, the calculation of "MaxPtsHighestPotnetialStars" is using only one value (April 2021's value = 78.67 because of ASC) of MaxTotalWHSS instead of applying thru different month's value. Where or how do I apply in DAX to follow the Processing Date for that particular month? I have created a third table between two tables for the ProcessingDate. Expected outcomes are: April: 78.67 May: 79.33 June: 79.33 Currently all same (wrong): April: 78.67 May: 78.67 June: 78.67 I have tried as below, but no luck yet: CALCULATE ( SELECTEDVALUE( vw_MxTtWghtdHlthSrvyScr[MaxTotalWHSS]), //TOPN (1, FILTER ( ALL(vw_MxTtWghtdHlthSrvyScr), CurrentTestValue <= vw_MxTtWghtdHlthSrvyScr[MaxTotalWHSS] && vw_MxTtWghtdHlthSrvyScr[ProcessingDate] = SELECTEDVALUE('Date Bridge'[ProcessingDate]) ) //vw_MxTtWghtdHlthSrvyScr[MaxTotalWHSS] //DESC //) )Replace blanks with zero (0) in Matrix Table
Hi Everyone, Been reading in this community on how to handle blanks with zero values and unfortunately, none of them works in my current situation. So I have this matrix table with Rows [Job Manager] & [InvoiceNumber], Columns [Month & Year], Values [Sales]. Been using these tricks below and none of them really works. Sales = IF ( SUM ( Invoices[Amount] ) = BLANK (), 0, SUM ( Invoices[Amount] ) ) Sales = SUM ( Invoices[Amount] ) + 0 Relationship modelNumber of customers by aggregated customer level
Hello I need a little help with this. The data contains the interest of the customers, this determines the interest of the customer on a given day. I need to be able to determine at any point in time how many customers had the level at their last assessment. I have tried to solve it with the following measure. Count_MDM = VAR Var_Day = CALCULATE ( MAX ( CalendarDIM[Date] ), ALL ( StagesDIM ) ) VAR Var_Stage = CALCULATE ( MAX ( StagesDIM[Stage Level] ), ALL ( CalendarDIM ) ) VAR Var_calc_Table = CALCULATETABLE ( StagesFact, ALL ( CalendarDIM ), ALL ( StagesDIM ), CalendarDIM[Date] <= Var_Day ) VAR Var_1 = SUMMARIZE ( Var_calc_Table, eu_account_vw[Customer ID], "stage_now", LASTNONBLANKVALUE ( StagesDIM[Stage Level], MAX ( StagesDIM[Stage Level] ) ) ) VAR Var_2 = FILTER ( Var_1, [stage_now] = Var_Stage ) VAR Var_3 = COUNTX ( Var_2, MAX ( [stage_now] ) ) RETURN Var_3 The output is as follows. The output is almost right, only where the combination of calendar week and the level (stage name) has no new entries, it’s Blank instead of the aggregated value. If I make a Calculated Table out of the Measure and return Var_3, it looks about right. So I suspect it is due to the filter context. I look forward to your inputs, thanks in advance.
