How calculate subscription based units on Month wise MoM Like Beginning of Month, Added in the Month
Hi Folks, I am connecting from snowflake Datamart to Power BI Using View. I have retriving Created_dt, Year,Month,ID,Name, sum(Units) from the view. It is subscription based business. I want to display Beginning of Month units, Added in the Month Units, End of the month units. Growth Can you please help me How to create dax calculation to achive the oupt report. It is for all the months across the years. Thank you very much in advance. Data: Created_dt Year_Created Month_Created ID Name UNITS 6/14/2022 2022 6 1175 Planet Entertainment 52973 7/26/2022 2022 7 1185 sony Entertainement 5758 9/19/2022 2022 9 204 Amazon Entertainement 27594 10/26/2022 2022 10 1224 Netflix 25692 10/30/2022 2022 10 1228 Z5 0 1/4/2023 2023 1 1282 Aha 22475 3/13/2023 2023 3 1336 JioHotstar 4280 4/3/2023 2023 4 1373 MX Player 0 6/9/2022 2022 6 1172 Voot 26240 8/1/2022 2022 8 1189 Youtube 204104 10/25/2022 2022 10 1221 Eros Now 36778 10/30/2022 2022 10 1227 Alt Balaji 448957 1/19/2023 2023 1 1111 Discovery ++ 232743 Required OutPut Report:Dax Help for counting blank cells for average sales
Hi All, I have an issue. I'm tryin gto find the average of sales Units which is equal to sum(sales units) / number of selected months. This average sales unit is used as denominator for another calucaltion which is months cover = sum(stock oh hand)/ avergae sales units. Months cover = sum(stock on hand)/Average sales units Average sales Units = sum(sales units)/number of selected months. This number of months is a dynamic number as the filter for date could be 'Next 6 months'/'Next 3 months' / CY-Month year/Next 12 Months and so on. These are options available in the date table. here's the current dax which works as expected - VAR totlsales = CALCULATE( SUM ('Sales - Forecast'[Sales_Units]), LASTDATE( CALCULATETABLE(VALUES('Date Snapshot'[Date]),'Date Snapshot'[RowsInSalesFC] = TRUE) ) ) VAR selmnths = CALCULATE( COUNTROWS('Date'), LASTDATE( CALCULATETABLE( VALUES('Date Snapshot'[Date]), 'Date Snapshot'[RowsInSalesFC] = TRUE ) ), 'Date'[Date] IN VALUES('Sales - Forecast'[Forecast_Month_Key]) ) VAR avgSales = DIVIDE(totlsales,selmnths,1) RETURN DIVIDE('Stock On Hand - Forecast'[Stock On Hand Units (Forecast)],avgsales,0) The Issue with the above calculation is it doesnt not consider the dates which are a blank for sales forecast units. SO with a date filter of next 6 months if a style colour has sales units as below - CURRENt RESULT - In the first style (1.) this calculation of avergae sales does a 20/4 = 5 as the Oct and Nov months sales Fcst Units is blank. EXPECTED RESULT - 20/6 = 3.33 Note - This condition is used as we have forecasts the at the 1st of every month . 'Date'[Date] IN VALUES('Sales - Forecast'[Forecast_Month_Key]) So Date column have all dates day/month/year vice and relative date filters like current month year (which is equated to 1/1/1990 as shown in screenshot above), Next 6 months/Next 3months . Fact table sales Forecast has a monthly snapshot of all forecast months. Here's an exaample - The current month snapshot will have 1st of the month and the today's snapshot. Style Forecast Month Snapshot date sales FCst Units ABC 1/05/2025 21/05/2025 0.01 ABC 1/06/2025 21/05/2025 0.02 ABC 1/07/2025 21/05/2025 0.03 ABC 1/05/2025 1/05/2025 0.05 ABC 1/06/2025 1/05/2025 0.06 ABC 1/07/2025 1/05/2025 0.07 ABC 1/05/2025 1/04/2025 0.17 ABC 1/06/2025 1/04/2025 0.11 Please advice how to include the dates irrespective of what values they hold mainly blanks and get the avergae right.
DAX: skip row by criteria?
Hi experts, I have a DAX challenge which is driving me crazy and I hope I can explain this in a understandable way: I have this table showing the time flow for refilling of products which has been sold out ("Empty") in a cupboard. Each timestamp is registered with either: Event="Refill" (=>time when the cupboard was filled again with new products) Event="Empty" (=>time when the last product in the cupboard was sold) I want to make two measures: 1) A measure "Next_Refill_Time", which - for each product - calculates the time between Event="Empty" and the first subsequent Event="Refill" (ref.1 in illustration below). BUT......(and here comes the tricky part).......IF the time between Event="Refill" and the first subsequent Event="Empty" IS LESS than 15 minutes, I need the calculation to jump to the next subsequent Event="Refill"? (ref.2 below) 2) a measure "Time_to_next_Refill(hours), which calculates the hours spent from a product is sold out ("empty") to the cuboard is "refilled" Example: I have this illustration, which tries to visualize the logic (please see the .pbix file attached) In my attempt to make the measures mentioned, I have made the following: Measure: "Next_Refill_Time" Next_Refill_Time_ = VAR SoldOutEndTime = SELECTEDVALUE('Table'[DateTime]) VAR FirstRefillTime = CALCULATE( MIN('Table'[DateTime]), ALL('Table'), 'Table'[Event] = "ReFill", 'Table'[Cupboard_ID] = SELECTEDVALUE('Table'[Cupboard_ID]), 'Table'[DateTime] > SoldOutEndTime ) VAR JumpValue=IF(DATEDIFF(SoldOutEndTime,FirstRefillTime,MINUTE) < ABS(15),BLANK(),FirstRefillTime) RETURN JumpValue Measure: "Time_to_next_Refill(hours) Total_SoldOut_Hours_ = SUMX( FILTER( 'Table', 'Table'[Event] = "Empty" && ( NOT NOT(ISBLANK(SELECTEDVALUE('Table'[Cupboard_ID]))) || 'Table'[Cupboard_ID] = SELECTEDVALUE('Table'[Cupboard_ID]) ) && ( NOT NOT(ISBLANK(SELECTEDVALUE('Table'[Product]))) || 'Table'[Product] = SELECTEDVALUE('Table'[Product]) ) ), VAR SoldOutEndTime = 'Table'[DateTime] // "FirstRefillTime": The earliest "ReFill" event that occurs after SoldOutEndTime for the same Back_Stock_Unit_ID. VAR FirstRefillTime = CALCULATE( MIN('Table'[DateTime]), ALL('Table'), 'Table'[Event] = "ReFill", 'Table'[Cupboard_ID] = EARLIER('Table'[Cupboard_ID]), 'Table'[DateTime] > SoldOutEndTime ) // NextSoldOutTime: The earliest "SoldOut" event occurring within 15 minutes after FirstRefillTime for the same Back_Stock_Unit_ID VAR NextSoldOutTime = CALCULATE( MIN('Table'[DateTime]), ALL('Table'), 'Table'[Event] = "Empty", 'Table'[Cupboard_ID] = EARLIER('Table'[Cupboard_ID]), 'Table'[DateTime] > FirstRefillTime, 'Table'[DateTime] <= FirstRefillTime + TIME(0, 15, 0) ) VAR IsValidPeriod = ISBLANK(NextSoldOutTime) VAR ValidRefillTime = IF(ISBLANK(FirstRefillTime), NOW(), FirstRefillTime) VAR SoldOutDuration = IF( IsValidPeriod, DATEDIFF(SoldOutEndTime, ValidRefillTime, MINUTE) / 60.0, 0 ) RETURN SoldOutDuration ) Which gives this result: ad.1 ) as you can see the part of the calculation, which finds the "next refill"-date/time appears to work. But for some reason, I can't get the "<15 min"-criteria to work? ad.2) again, the hour-calculation appears to work between the "Empty" and the "Refill" date/time. But here the "<15 min"-criteria doesn't work either? Please, If anyone can help me to get closer to the solution I need, It will be greatly appreciated. I have the .pbix model to share if needed ( apparently, I can't attach the model here) And if my explanation is insufficient, please let me know? Thanks. Br, jayjay0306
Assistance Needed: Identifying New and Lost Customers
Dear Power BI Community, I hope this message finds you well. I am seeking your guidance on a scenario where I need to identify the number of new customers acquired in a specific or selected month and the number of customers lost during the same period/selected month. In my dataset, I have two columns: "File Date" and "Data Date", but the focus should be on the "Data Date" column for this analysis. Below are the measures I have used in my file: Sample Data This is my old post on community for your reference https://community.fabric.microsoft.com/t5/Desktop/Identified-new-entry-and-missing-product-from-last-month-Part-2/m-p/936895#M448841 Result should be like below in visual Reference table for results Resent in chosen month = Countrows ( ChangeInCust ) Present in previous month = CALCULATE ( [Resent in chosen month], PREVIOUSMONTH ( ChangeInCust [Data Date] ) Cloesed this month = COUNTROWS ( FILTER ( SUMMARIZE ( CALCULATETABLE ( VALUES ( ChangeInCust [CustID] ), DATESBETWEEN ( 'Calendar'[Date], EDATE ( MIN ( 'Calendar'[Date] ), -1 ), MAX ( 'Calendar'[Date] ) ) ), ChangeInCust [CustID], "ABCD", [Resent in chosen month], "EFGH", [Present in previous month] ), [EFGH] > 0 && [ABCD] = 0 ) ) New this month = COUNTROWS ( FILTER ( SUMMARIZE ( VALUES ( ChangeInCust [CustID] ), ChangeInCust [CustID], "ABCD", [Resent in chosen month], "IJKL", CALCULATE ( [Resent in chosen month], DATESBETWEEN ( 'Calendar'[Date], MINX ( ALL ( 'Calendar' ), 'Calendar'[Date] ), EOMONTH ( MIN ( 'Calendar'[Date] ), -1 ) ) ) ), [ABCD] > 0 && [IJKL] = 0 ) ) Closed this month 1 = COUNTROWS ( FILTER ( SUMMARIZE ( CALCULATETABLE ( VALUES ChangeInCust [CustID]), DATESBETWEEN ( 'Calendar'[Date], DATE( YEAR(MIN('Calendar'[Date])), MONTH(MIN('Calendar'[Date])) , DAY(MIN('Calendar'[Date])) ), MAX ( 'Calendar'[Date] ) ) ), ChangeInCust [CustID], "ABCD", [Resent in chosen month], "EFGH", [Present in previous month] ), [EFGH] > 0 && [ABCD] = 0 ) ) I would be extremely grateful if you could help me refine or improve this approach. Your expertise and insights would mean a lot. Thank you in advance for your valuable time and support! Best regards, Amardeep BhingardeveHow do I filer product on index?
Hi people, I hope you can help me with a complex DAX problem: I have a report based on the following tables: D_Customer D_Date F_Sale Each customer has day-to-day sale in the table: example: now, some of the sales dates for each customer are "marked" for various reasons. On these "marked" dates, I try to do the following: on each marked date I want to 1) "Date mark - 14 (Sum)" = sum the sales for 14 days prior to the marked date 2) "Date mark +14 (Sum)" = sum the sales for 14 days after the marked date 3) make an index % = "Date mark + 14 (Sum)" / "Date mark - 14 (Sum)" example (illustrated): 4) once I have calculated the "Date mark + 14 (Sum)", "Date mark - 14 (Sum)" and Index%, I need to make the average Index% for all marked dates on each customer: example (illustrated): This I have managed with the following measure: Date-14 (index)avg per cust = AVERAGEX( CALCULATETABLE(VALUES('Calendar'[date]), ALLSELECTED('Calendar'[date]), 'Calendar'[Marked_Date]), VAR varReportDate = CALCULATE(MAX('Calendar'[date])) VAR Summinus14= CALCULATE( SUM( 'Sales'[Sales]), FILTER( ALL( 'Calendar'[date] ), 'Calendar'[date] >= varReportDate-14&&'Calendar'[date] < varReportDate ) ) VAR Sumplus14= CALCULATE( SUM( 'Sales'[Sales] ), FILTER( ALL( 'Calendar'[date] ), 'Calendar'[date] <= varReportDate+14&&'Calendar'[date] > varReportDate ) ) RETURN DIVIDE(Sumplus14,Summinus14) ) It works, BUT I also need to filter the measure on specified products from the product-table (‘Product’ [Product Name]). I have tried the following, but the outcome is “0”, when I slice on a "product name"?: Date-14 (index)avg pr cust = AVERAGEX( CALCULATETABLE(VALUES('Calendar'[date]), ALLSELECTED('Calendar'[date]), 'Visits Executed'[Visit_Date]), VAR varReportDate = CALCULATE(MAX('Calendar'[date])) VAR varProduct=SELECTEDVALUE('Product'[Product Name]) VAR Summinus14= CALCULATE( SUM( 'KPI Sell Out'[Sales Out Packs CY]), FILTER( ALL( 'Calendar'[date] ), 'Calendar'[date] >= varReportDate-14&&'Calendar'[date] < varReportDate ), 'Product'[Product Name]=varProduct ) VAR Sumplus14= CALCULATE( SUM( 'KPI Sell Out'[Sales Out Packs CY] ), FILTER( ALL( 'Calendar'[date] ), 'Calendar'[date] <= varReportDate+14&&'Calendar'[date] > varReportDate ), 'Product'[Product Name]=varProduct ) RETURN DIVIDE(Sumplus14,Summinus14) ) How do I add a filter in the measure here, so I only get the sales index % on the product chosen? any input or suggestion will be greatly appreciated. Thanks. Br, JayJay0306
