Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Date issues on a custom date

Hello,   I have issues to associate dates correctly. I don't really know how to workaround this. Note: the second table is a view and i can modify it for my needs. I have dimension date table a...
Date and Time
segmentation
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , the requirement is not very clear.

dateadd should be used on the date table

 

example

 

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing 4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello amitchandak ,

     

    you reply me each time and i thank you for that but CALCULATE(SUM doesn,'t work in my case as my field is a string !


    I have a date column in my data table wich i want to link with my date table.
    I want to retrieve all the data wich corresponding to the generic_period colum. I would have the same for Month-1, Month-2 etc ...

    For exemple: if i choose in my report segment period 30/09/2019, i would also retrieve data from the previous report period (31/08/2019), but it is blank in my result table.

    This seems not work ? i am doing something wrong here ? How could i handle it ?

     

     

    test Q-1 = 
CALCULATE(DISTINCTCOUNT('Customers'[Customer]),
filter('Date', ENDOFMONTH(DATEADD('Date'[Date],-1,MONTH)) IN (ALLSELECTED(Customers[Generic_Period])))
)

     

     

    My date table is generated like this:

     

     

    Date = CALENDAR(DATE(2018,1,1),DATE(2030,12,31))