Prior month DAX using Measure are not displaying any value
Hi everyone, Need your expertise on this issue. What I want to do is to get the variance between current month and last month. But now I am trying to detect the value of last month first before calculate the variance (just minus-ing the value of current month and last month). Unfortunately, the value is not displaying eventhough I have tried all the other methods that are available online. Maybe I missed something? Need your help for this one :') Here is the fact table : Table A We will need to take the last value of EndDateHourDateTime column as it will keep on adding and accumulate throughout the day. For this problem, we are just going to focus on the value of Defect Class = "Produced Glove". There are also ID column which I didnt include in this screenshot. DimDateTime table : Table DimEndDateHourDateTime Just for reference in case you need to refer the data type available inside this table. Measure 1 : To calculate the sum of the Produced Glove Total Produced Glove = Calculate(Sum(A[Value]), Filter(A, A[DefectClass] == "Produced Glove")) Measure 2 : To calculate the previous month of the Produced Glove (below are some of the failed attempts, a bit messy.. sorry)(tried parallelperiod also, but failed.. sobs) Total Produced Glove last Month = var a = VALUES(DimEndDateHourDateTime[FullDateAlternateKey]) var b = DATEADD(a, -1, MONTH) var c = Calculate(Sum(A[Value]), Filter(A, A[DefectClass] == "Produced Glove")) return Calculate(Sum(A[Value]), Filter(ALL(DimEndDateHourDateTime), DimEndDateHourDateTime[FullDateAlternateKey] in b ), Filter(A, A[DefectClass] == "Produced Glove")) @ Variance = //var a = CALCULATE([@ Card_Date], DATEADD(DimEndDateHourDateTime[FullDateAlternateKey], -1, MONTH)) CALCULATE(SUM(A[Value]), PREVIOUSMONTH(DimEndDateHourDateTime[FullDateAlternateKey])) Not sure, where went wrong.. my table become like this.. not displaying the value Need your help for this one :')
Moving result of a month as an opening balance to the next month
Hi, I would like to move summary result of one month as an opening balance to the next month. Something like that: Jan 2022 Feb 2022 Mar 2022 DAX formula A (closing balance from previous month) 10 20 28 DAX formula B 2 5 DAX formula C 5 1 DAX formula D 3 2 DAX formula E (Summary of A-D) 20 28 I use for "DAX formula A" PREVIOUSMONTH formula but then I get circular dependency between "DAX formula E" (Summary of A-D) and "DAX formula A". "DAX formulas B-D" are calculations of sales, new orders so it is not taken directly from any table, it is also calculated. Could anybody help?
Previous Month goes blank on some Month
Hello, I have problem with previous month dax. I want to show last month sales revenue based on the slicer, but when I select year of 2022 and month in February, this 'last month' card should display the sum of revenue in January but it shows ((blank)). I tried to change the year to 2021 and it worked fine. Below the captures: I already have 2 tables, 1 table is for data and another 1 for calendar, below are my codes: last month = CALCULATE ( SUM('190222'[daily_revenue]), PREVIOUSMONTH ( 'Calendar'[Date].[Date] ) ) And this is the table relationship: This is the result when I tried to change the year to 2021: Please help if there is something that I skipped to make it error. Thanks in advance!
PREVIOUSMONTH displays blank
Hello, I have a date column with the same date all the way down (7/2/2021) This works fine: Last Month = FIRSTDATE('data basis report'[Date of Data]) - Displays 'July 2021' as I expect. Date of Data is formatted as a Date field. This displays blank: Last Month = PREVIOUSMONTH(FIRSTDATE('data basis report'[Date of Data])) - I'd like it to display June or June 2021 Any ideas? Thanks in advance for the help..
Return Previous Month's Sales based on whether item is promoted during current month
Hi all, I am trying to create a measure to return previous month's sales for an item that is on promotion during the current month. I created a dummy data set below that illustrates what I am trying to achieve. The promotion column indicates 0 if it is not on promotion and 1 if it is on Promotion. Item Date Promotion Indicator Sales TV 1 2/1/2020 0 $50 TV 1 2/2/2020 0 $100 TV 1 2/3/2020 0 $80 TV 1 3/1/2020 1 $150 TV 1 3/2/2020 1 $200 TV 1 3/3/2020 1 $175 TV 2 2/1/2020 1 $120 TV 2 2/2/2020 1 $130 TV 2 2/3/2020 1 $115 TV 2 3/1/2020 0 $70 TV 2 3/2/2020 0 $50 TV 2 3/3/2020 0 $60 Desired Result: Slicer: March Item Promotion Sales Previous Period Sales TV 1 $525 $230 The main challenge is that if I create a calculate function that filters on the promotion indicator, I end up with something like this (which is not desireable): Slicer: March Item Promotion Sales Previous Period Sales TV 1 $525 $0 TV 2 $0 $365 Current dax: Previous Period Sales = calculate(sum(Sales), Promotion Indicator = 1, previousmonth (date)) Thank you so much for your help!
