Forum Discussion

majdkaid22's avatar
majdkaid22
Icon for Helper V rankHelper V
9 years ago
Solved

MTD measure

Hi guys,   my MTD & YTD measure show the figures up to t-1 (last calender day) even though there are transactions done today, they won't be included.    This is my measure:  MTD Deposits = CALCUL...
Need Help
  • majdkaid22's avatar
    majdkaid22
    9 years ago

    Anonymous Many thanks mate. 

     

    Still for some reason, what you have suggested did give me blank figures when I filter "current month" however you gave me the idea of doing the following:

     

    I created a duplicate column for (meAccountTransaction[TransactionDate]) changed the format to date only and then created a direct relationship between (meAccountTransaction[TransactionDate]) and (meCalendar[DateValue]) 

     

    Appreciate your help, it was a spot on. am getting the figures up to the minute!

  • Anonymous's avatar
    Anonymous
    9 years ago

    The columns in the relationship must be the same. If one is datetime and the other is date only, there will be zero matches between the two columns.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

majdkaid22 I cannot replicate that. It works fine in my test. Are you sure there's not another visual level filter on that blank card?

 

Silly question, you do have a table relationship between meAccountTransaction[TransactionDate] and meCalendar[DateValue] right?

majdkaid22's avatar
majdkaid22
Icon for Helper V rankHelper V
9 years ago

Anonymous Yes between ValueDate in Calander and TransactionDate in meAccountTransaction 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    majdkaid22 almost. When you create a second relationship between the same two tables the new relationship is inactive. This means that you have to explicitly tell formulas to use it. By default all formulas will use that first relationship. So your MTD formula should now be:

     

    MTD Deposits = CALCULATE(
	SUM(meAccountTransaction[Deposits]),
	DATESMTD(meCalendar[DateValue]),
	USERELATIONSHIP(
		meAccountTransaction[TransactionDate],
		meCalendar[DateValue] 
	)
)
  • majdkaid22's avatar
    majdkaid22
    Icon for Helper V rankHelper V
    9 years ago

    Anonymous apologies, the relationship is between CalendarDateID and PostingDateID

     

    I think my DateValue and TransactionDate need to have the same format before they can be linked together no? 

     

    If the relationship then between those 2, it should be all solved? 

     

     

  • majdkaid22's avatar
    majdkaid22
    Icon for Helper V rankHelper V
    9 years ago

    Anonymous Many thanks mate. 

     

    Still for some reason, what you have suggested did give me blank figures when I filter "current month" however you gave me the idea of doing the following:

     

    I created a duplicate column for (meAccountTransaction[TransactionDate]) changed the format to date only and then created a direct relationship between (meAccountTransaction[TransactionDate]) and (meCalendar[DateValue]) 

     

    Appreciate your help, it was a spot on. am getting the figures up to the minute!

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    The columns in the relationship must be the same. If one is datetime and the other is date only, there will be zero matches between the two columns.