Forum Discussion
Comparing sales with last year
Hi Guys,
I need to compare the current year MTD sales vs Last year same month MTD sales i.e say Current month is march 2019, I need to compare Current year march 2019 with last year march 2018 Dynamically. i used a dax expression as
MTD_CY = CALCULATE(DISTINCTCOUNT(Statement[name]),DATESMTD(Date[Date]))
MTD_LY = CALCULATE(DISTINCTCOUNT(Statement[name]),DATESYTD(DATEADD(Date[Date],-1,YEAR)))
But when i use this Dax i can get values for the current year say 2019 but my last year valus 2018 shows blank. I connecting the datasource with SQL in the Direct Query mode. Please help me and provide a solution.
11 Replies
- amitchandak
Super User
Hope, Month and year are coming from Date table. It has also been marked as date.
Step to mark date table : https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intelligence-in/ba-p/922885
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin- amitchandak
In checked on import data. As in my direct query (I do not have month as of now).
And I am able to get last year's data correctly(With year and month slicer). Can you just change your year 2018 and check are you getting MTD data.
- Ashish_Mathur
Hi,
What result do you get with this?
MTD_LY = CALCULATE([MTD_CY],SAMEPERIODLASTYEAR(Date[Date]))
If it does not work, then share the link from where i can download your PBI file.
I need to calculate the Last year YTD value. Say if today's date is Feb 24th 2020 i need to compare current year YTD value to last year YTD value i.e till 24th Feb 2019. I used a formula as
YTD= CALCULATE([Enquiry],DATESYTD(Date[Date]))YTD_LY = CALCULATE(YTD,SAMEPERIODLASTYEAR(Date[Date]))When i use this query i getting last year value till 28th Febuary 2019 but i need to get only till 24th Feb 2019 since my current date is 24th Feb 2020
