Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 as follow:
Capture.PNG

I have an other table with products and dates like this (i show you only dates here):
Capture2.PNG

I made a relation between them:
Capture3.PNG

I want to count customer by entity for a specific report period, so i made a segment on the Generic_Period.
I created mesures for this segment:

Customers = 
CALCULATE(DISTINCTCOUNT('Customers'[Customer]), 
filter(Customers, Customers[Generic_Period] = DATEADD(Customers[Generic_Period],0,MONTH)))

Customers M-1 = 
CALCULATE(DISTINCTCOUNT('Customers'[Customer]), 
filter(Customers, Customers[Generic_Period] = DATEADD(Customers[Generic_Period],-1,MONTH)))


The first probleme is Q-1 returns 0 values :
Capture4.PNG

The second problem, It doesn't work if the day number isn't the same as previous month.
If i select 30/09/2019 it will not retrieve the 31/10/2019 even with end of month -1 like this :

ENDOFMONTH(DATEADD(Subscription_Partner_by_month[Generic_Period],-1,MONTH))

 

Thanks a lot for your time.

 

3 REPLIES 3
Anonymous
Not applicable

even with this value, now it work in a card but my M-1 column is still empty.

EOMONTH(ALLSELECTED(Customers[Generic_Period]),-1)

 

amitchandak
Super User
Super User

@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
Not applicable

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))

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors