I have a data JAN-19. How to get JAN-18 In direct query. FORMAT is not working in direct query

[anileshknpowerb]

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

[amitchandak · Accepted 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

[Icey]

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

[amitchandak]

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

[anileshknpowerb]

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

[Ashish_Mathur]

Hi,

See if this calculated column formula works

=EDATE(1*("1-"&Data[Date]),-1)

Hope this helps.

[amitchandak · Accepted 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

[anileshknpowerb · Accepted solution]

It worked for me. Thanks a lot.