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.
How do I sum index on multiple dates?
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): This I have managed with the following measure: Index % = VAR MarkedDateToday= MAX('Calendar'[Marked_Date]) VAR varReportDate = SELECTEDVALUE( '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 ) ) VAR Index_calc=DIVIDE(Sumplus14,Summinus14) RETURN IFERROR(IF(MarkedDateToday,Index_calc,0),BLANK()) BUT - and here is my challange: 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: Does it make sense? my problem is: how do I sum the "index %"-calculation for all relevant "marked"-dates on for each customer? any input or suggestion will be greatly appreciated. Br, JayJay0306
DAX measure CALCULATE budget context
Hello, I'm looking for the correct DAX measure of how to return the correct budget by having following condition. One slicer "Report Period" always required. Relevant budget need to be returned, based on selected period. This is an example of the report period and expected corresponding budget value. ... I'm grateful for any tips on how I can get this PowerBI DAX measure to work, on these expected conditions.
Time Intelligence: Measure within a Measure within a Measure
Hi, I have the below measure: Count of Apples = DISTINCTCOUNT('TableA'[Apple_SK]) I now want to use this measure within a second measure: Count of Apples Exited = CALCULATE( [Count of Apples], NOT ISBLANK(TableB[Apple Exit Date]) ) TableA -> TableB on Apple_SK I now want to use the second measure in a third measure: Count of Apples Rolling 1 Qtr = VAR __lastDate = LASTDATE( TableB[Apple Exit Date] ) VAR __result = CALCULATE( [Count of Apples Exited], DATESINPERIOD( TableB[Apple Exit Date], __lastDate, -1, QUARTER ) ) RETURN __result I only get the correct result from my third measure if I use the first measure and not the second measure within it... Count of Apples Rolling 1 Qtr = VAR __lastDate = LASTDATE( TableB[Apple Exit Date] ) VAR __result = CALCULATE( [Count of Apples], DATESINPERIOD( TableB[Apple Exit Date], __lastDate, -1, QUARTER ) ) RETURN __result Why can I not use [Count of Apples Exited] to calculate the rolling latest quarter result? The [Count of Apples Exited] result is returned, as if the DATESINPERIOD filter is not being applied? Thanks.
Calculating Percentage with Filter on a Specific Column
Hello Everyone, This is the table that I have: I want to calculate the Percentage of Effort for specific projects (last column in the picture). For example, The total effort for Project 1 is 30, so 10/30= 0.3333 and 20/30=0.6667 would give me what I want. The Dax Code Would be something like this: DIVIDE(Effort, SUM(Effort)) except the sum is calculated for all efforts(which would be 50 instead of 30). I don't know how to do the filtering such that this division is calculated on specific projects. Could someone guide me? Thans in advance.HELP!! Add period 0 to calculation without putting it in slicer
Hi everyone, I've been running into a problem for a while now. I am currently working on a financial balance sheet. Currently my dataset consists of the following columns: - Description - Year_Open - Period credit (These are the changes to the opening value) - Period debit (These are the changes to the opening value) - Period (1 to 12 months) As can be seen in the photo above, the opening value is not a period, I wanted to give it the period 0. This made me want to create the formula: Period 0 + selected period in slicer. Does anyone know how I can create this formula in DAX? I also have to ensure that the period 0 is not visible in the slicer, how can I do that? However, I am not very good with DAX formulas and so far I have solved it as shown below. Only then will the total not change if I adjust the slicer, even though this is a requirement. Credit = VAR PeriodCredit = SUM( 'Balance'[Year_Open]) + SUM('Balance'[Period_Debit]) - SUM('Balance'[Period_Credit]) VAR Scoop = NOT(ISINSCOPE(KPI_EOL_xml_GLAccountClassifications[GLClassification2_description])) VAR ScoopPeriodCredit = SUM(Balance[YearClosedCredit]) VAR Result = IF(Scoop,ScoopPeriodCredit,IF(PeriodCredit < 0, - PeriodCredit)) Return Result Hopefully someone can help me with this! Thanks!
Create a group of filters to reuse in subsequest measures.
Hi All, I'm working on a dashboard and I'm creating some measures. For example Sales COGS Profit Net Profit GP I would like to display both sold and pending on a single matrix. The question is, can I avoid passing through filter conditions that are the same. The four filters would need to be applied to each calculate statement. Is there a more optimal method of stating the filter group once and referencing it throughout the model. i.e. GP = CALCULATE(sum(Sample[GP]), Sample[NUMBER]=340, Sample[Customer]<>0, Sample[Store_Number]<>70, Sample[CATEGORY] = "Sold") GP = CALCULATE(sum(Sample[Sales]), Sample[NUMBER]=340, Sample[Customer]<>0, Sample[Store_Number]<>70, Sample[CATEGORY] = "Sold")
