Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I have a figure that I count for the month and I want to create a field that will show me the count compared to the same day last month.
I am using direct query so some of the suggestions I have already seen don't work with it.
Any advice would be much appreciated.
Thanks
Solved! Go to Solution.
@Anonymous ,
You could modify the measures like pattern below:
Total Sales Diff = VAR First_Day_Current_Month = EOMONTH ( Table[Date], -1 ) + 1 VAR Third_Day_Current_Month = EOMONTH ( Table[Date], -1 ) + 3 VAR First_Day_Last_Month = EOMONTH ( Table[Date], -2 ) + 1 VAR Third_Day_Last_Month = EOMONTH ( Table[Date], -2 ) + 3 VAR Total_Sales_Current_Month = CALCULATE ( SUM ( Table[Sales] ), FILTER ( Table, Table[Date] >= First_Day_Current_Month && Table[Date] <= Third_Day_Current_Month ) ) VAR Total_Sales_Last_Month = CALCULATE ( SUM ( Table[Sales] ), FILTER ( Table, Table[Date] >= First_Day_Last_Month && Table[Date] <= Third_Day_Last_Month ) ) RETURN Total_Sales_Current_Month - Total_Sales_Last_Month
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
So a very simple example would be if I had a list of dates and a count beside them:
01/01/2019 - 10
02/01/2019 - 20
03/01/2019 - 70
.
.
.
.
01/02/2019 - 20
02/02/2019 - 40
03/02/2019 - 100
So far in February I have 160 and I want to compare that to the first 3 days in january. So my calculation would tell me that I am +60 as there have been 60 more in feb than jan for the same time period.
That is the kind of calculation I am looking for.
Thanks
@Anonymous ,
You could modify the measures like pattern below:
Total Sales Diff = VAR First_Day_Current_Month = EOMONTH ( Table[Date], -1 ) + 1 VAR Third_Day_Current_Month = EOMONTH ( Table[Date], -1 ) + 3 VAR First_Day_Last_Month = EOMONTH ( Table[Date], -2 ) + 1 VAR Third_Day_Last_Month = EOMONTH ( Table[Date], -2 ) + 3 VAR Total_Sales_Current_Month = CALCULATE ( SUM ( Table[Sales] ), FILTER ( Table, Table[Date] >= First_Day_Current_Month && Table[Date] <= Third_Day_Current_Month ) ) VAR Total_Sales_Last_Month = CALCULATE ( SUM ( Table[Sales] ), FILTER ( Table, Table[Date] >= First_Day_Last_Month && Table[Date] <= Third_Day_Last_Month ) ) RETURN Total_Sales_Current_Month - Total_Sales_Last_Month
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous ,
So suppose you want to achieve sales in same day last month, you can create a measure using DAX like pattern below:
Total Sales = VAR Same_Day_Last_Month = EDATE ( Table[Date], -1 ) RETURN CALCULATE ( SUM ( Table[Sales] ), FILTER ( Table, Table[Date] = Same_Day_Last_Month ) )
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sample data and expected output would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.
That being said, can you calculate the date like:
Measure = DATE(YEAR([Date]),MONTH([Date])-1,DAY([Date]))
Something along those lines? Obviously will have to account for January dialing back to December of last year but you could handle that with an IF statement or SWITCH.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |