Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a data in MON-YY(Ex: JAN-19). I would like to get data as JAN-18(12 months ago). How can I get this using direct query option? I cant use FORMAT function in directquery mode.
Thanks,
Anilesh
Solved! Go to Solution.
Please try like this
Measure 3 =
Var _max = maxx(datedim,datedim[Month_Year])
Var _lastYear = left(_max,4) & (RIGHT(_max,4)-1)
return
CALCULATE([Net Sales],FILTER(all(datedim),datedim[Month_Year]=_lastYear))
In your case you have to use (RIGHT(_max,2)-1), As you have YY
Hi @anileshknpowerb ,
Is this problem solved?
If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, please let me know.
Best Regards
Icey
Is your date is date level. If yes, it better that you create mon-year there. You should also create a date table in the database.
To get that you can use datesmtd or totalmtd
Refer to my blog on the direct connection for time intelligence https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
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
My data is in JAN-19 format and there is no date column in database.
Can I use default date value 1 and derive Month and year from JAN-19. Can I derive JAN-18 by using this?
Thanks,
Anilesh
Hi,
See if this calculated column formula works
=EDATE(1*("1-"&Data[Date]),-1)
Hope this helps.
Please try like this
Measure 3 =
Var _max = maxx(datedim,datedim[Month_Year])
Var _lastYear = left(_max,4) & (RIGHT(_max,4)-1)
return
CALCULATE([Net Sales],FILTER(all(datedim),datedim[Month_Year]=_lastYear))
In your case you have to use (RIGHT(_max,2)-1), As you have YY
It worked for me. Thanks a lot.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |