Month To Date Comparisons

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

Enterprise%20DNA%20Expert%20-%20Small

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

image

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?

image

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

Enterprise%20DNA%20Expert%20-%20Small

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.

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!,