Forum Discussion

mwrdm's avatar
mwrdm
Frequent Visitor
4 years ago
Solved

DATESMTD and DATESYTD Showing Same Values

Hi all,

 

I'm having some trouble with the DATESMTD and DATESYTD DAX functions.

 

I have a measure for Cost of Goods Sold as follows:

 

Cost of Goods Sold =
CALCULATE(
SUM('Fact'[Debit/Credit Amount]),
Dim_GLAccount[G/L Account.Level 07] = "Cost of Good Sold"
)

The MTD measure I have is as follows:
 
Current MTD = CALCULATE([Cost of Goods Sold],DATESMTD(Dim_FiscalPeriod[Fiscal Period Start]))
 
YTD:
 
Current YTD = CALCULATE([Cost of Goods Sold],DATESYTD(Dim_FiscalPeriod[Fiscal Period Start]))
 
I get the same value for all three as below:

 

 

The fact table is correctly join to Dim_FiscalPeriod and Dim_FiscalPeriod is joined to CalendarTable (Dim_Date is connected to CalendarTable on Fiscal Period Start = Date).

 

 

The values change depending on which month is selected, so it is doing part of it correctly it seems.

Lastly, I get the same values if I use the CalendarTable.Date field in the two DAX functions. 

 

Thanks in advance for any help.

Need Help
  • mwrdm's avatar
    mwrdm
    4 years ago

    I believe I have figured it out. Since I have a filter currently selected for month, the DAX was not viewing data outside of the filter context.

    Changing the DAX to the following looks better. Now I just need to verify the data.

    Current MTD = CALCULATE([Cost of Goods Sold],ALL(Dim_FiscalPeriod),DATESMTD(Dim_FiscalPeriod[Fiscal Period Start]))

2 Replies

  • mwrdm's avatar
    mwrdm
    Frequent Visitor
    4 years ago

    I believe I have figured it out. Since I have a filter currently selected for month, the DAX was not viewing data outside of the filter context.

    Changing the DAX to the following looks better. Now I just need to verify the data.

    Current MTD = CALCULATE([Cost of Goods Sold],ALL(Dim_FiscalPeriod),DATESMTD(Dim_FiscalPeriod[Fiscal Period Start]))
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi mwrdm ,

      Does that make sense? If so, kindly mark your answer as the solution to close the case please. Thanks in advance. If not, please provide more details with your desired output and your pbix file without privacy information.

       

      Best Regards
      Community Support Team _ Polly

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.