Hi,
I am trying to create MTD TY vs MTD LY and a similar calculation for YTD but i’m having contiguous error while using SamePeriodLastYear and tried few other time intelligence functions.
I’m using a view with a date column and joined that with the date table created using your query.
Please help.
Please upload a sample PBIX file so we can see exactly what you have setup.
Thanks
This honestly shouldn’t be too difficult but it’s hard to know exactly what the answer is without seeing the DAX formulas you’re using or a demo model of the scenario you are dealing with.
This should be straight forward though if you have the right model setup with a date table and then utilizing the correct time intelligence functions.
Potentially use DATEADD here instead of SAMEPERIODLASTYEAR. It’s a little more flexible.
I do believe though this will be a simple fix if you can provide more info.
Thanks
Sam
Hi Gents,
Thanks for your response. Attached is the screenshot of my tables and what i’m trying to achieve with the
error.I will try the DATEADD function, and see whether it is helpful. Unfortunately I’m very new to power Bi and trying my head around with DAX.
MTD Sales = TOTALMTD([Total Sales],'Date Query'[Date])
MTD LY = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date Query'[Date]))
Regards,
Bala
Hi,
Dateadd gives me similar error too
This is simply your relationships not being correct here.
Can you send an image of the model your have?
You need to have a one to many relationship here from your date table to your fact table. All I can see is that you don’t currently have this setup correctly.
Sam
Hi,
I have an active relationship defined. Please see the attached image! And also none of the DAX time functions works for me! DATEADD, PARALLEL PERIOD etc…
Change the Cross filter direction to the below
You rarely need ‘Both’ or the security filters ever in my opinion
Sam
I did try that too, but no help.
Not sure whether i need to format the date field in my fact table.
I tried this simple calc,
MTD LY = DATEADD(view_MVX_SM_sales_with_rebate1_powerbi[accounting_date],-12,MONTH)
but still the same error. Anything to do with the date column not being unique or weekends missing? or a timestamp?
It looks like your field called accounting date does not have contiguous dates. Need to use your Date Query Date field. Your measure for MTD LY should look something like:
MTD LY = CALCULATE([Total Sales],DATEADD('Date Query'[Date],-12,MONTH))
It would make things easier if you were able to upload your PBIX file.
Thanks
Are you using a date table?
Yes this could also be the problem. That’s why it’s helpful to include all the information right at the beginning.
Also you are not using DATEADD correctly as well.
You need to use CALCULATE( measures, DATEADD… )
Not DATEADD just by itself. Definitely review the tutorial and copy exactly what is there.
You must use a date table when using time intelligence functions.
See below.
http://portal.enterprisedna.co/courses/103686/lectures/1772347
Also here
Sam
I made this to work using the syntax below
MTD LY = CALCULATE([Total Sales],DATEADD(FILTER(DATESMTD('Date Query'[Date]),'Date Query'[Date]<TODAY()),-12,MONTH))
Ok that’s great
Sam
Hi,
I have 2 data models, same uses similar Total Sales, MTD, QTD and YTD Calcs. but they react differently!.
Any reason that this could happen?
Regards,
Bala
Yes very likely because of the ‘initial context’ that you have in your tables. One has accounting_date and the other has date.
You always should be using the date column from the date table and not from any other table.
Thanks
Sam
Thanks! Yes I did realise this this morning and fixed it! Works fine now!,
