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
phjz
Frequent Visitor

Counting Days in a column

Hey all,

 

its really hard to explain, so I'll try to show you what I want within the following table. What I want is a formula for the column "Number of Days".

 

As the table doesn't has values for every date, the formula for "Number of Days" has to refer to a calendar-table (I guess...), which is already created. Otherwise, you could say, that the date of the first apperance of a product could get the day number 1, but then I don't know how to do count the days that are missing in this table.

 

VolDateProductNumber of Days
124.07.2022A1
224.07.2022A1
325.07.2022A2
226.07.2022A3
228.07.2022A5
129.07.2022B1

 

 

I hope this is understandable. 

 

Thank you very much in advance!

1 ACCEPTED SOLUTION

Hi, @phjz 

You can't apply a measure to the x-axis of line chart.

You need to convert your original measure formula to calculated column formula.

Column_Number of days =
VAR basedate =
    CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Product] ) )
RETURN
    DATEDIFF ( basedate, Table1[Date], DAY ) + 1

Best Regards,
Community Support Team _ Eason

View solution in original post

3 REPLIES 3
eliasayy
Impactful Individual
Impactful Individual

hello @phjz ,
please try :

Number of days = 
VAR basedate = CALCULATE(MIN(Table1[Date]),ALLEXCEPT(Table1,Table1[Product]))
return
DATEDIFF(basedate,SELECTEDVALUE(Table1[Date]),DAY) +1

 

Screenshot 2022-11-03 150515.png

phjz
Frequent Visitor

That does work really good - thank you so much!

 

There is one last problem with this: I want to compare Product A and B using a line diagram, but I can't put the measure on the x-axis. When I try to type the formula in the field for a new column, it doesn't work. Do you have an idea for this?

Hi, @phjz 

You can't apply a measure to the x-axis of line chart.

You need to convert your original measure formula to calculated column formula.

Column_Number of days =
VAR basedate =
    CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Product] ) )
RETURN
    DATEDIFF ( basedate, Table1[Date], DAY ) + 1

Best Regards,
Community Support Team _ Eason

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