Hey Team I need to create a line chart which shows the total number of 'vulnerabilities' for each month.I have a column for dates which corrosponds to a specific vulnerability that has occured. This column is formatted as DD-MM-YYYY There needs to be 2 different lines: 1 for vulnerabilities that are older than 30 days 1 for vulnerabilities that were less than 30 days old. Something like this: Thanks

Hi there, The problem with this is that I dont have a column that confirms if the occurence is N > 30 or N < 30.I'm trying to create one now, here is my DAX code for the new column: AGE of CRASH = if (DATEDIFF([Time of Crash ],TODAY(),DAY)>30, "N > 30 ","N < 30") How do i adjust this for M code reqs? Thanks

Line Chart 30 Days for Each month | Microsoft Fabric Community

9 Replies

Anonymous
Not applicable
5 years ago
amitchandak
Super User
5 years ago
Anonymous , Need data to suggest

but will be like this

vulnerabilities <30 days =
countx(values(Table[vulnerability_id]), if(Datediff(Min[vulnerability Date], max(date[Date]), day)<30, [vulnerability_id], blank()) // you can use min(date[Date]), if needed

vulnerabilities >30 days =
countx(values(Table[vulnerability_id]), if(Datediff(Min[vulnerability Date], max(date[Date]), day)>30, [vulnerability_id], blank()) // you can use min(date[Date]), if needed

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Anonymous
Not applicable
5 years ago
Hi Anonymous ,

Based on your description, you can create a calculated column as follows.
My test table:
test = IF(DATEDIFF([date],TODAY(),DAY)>30, "earlier than 30 days","in 30 days")
Then drag it to the "legend" field.

Result:
Hope that's what you were looking for.
Best Regards,
Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Anonymous
  Not applicable
  5 years ago
  This does work however I require the Legend Field for a different Column.
  Thanks for your effort
Anonymous
Not applicable
5 years ago
Hi Anonymous ,

Maybe I'm not getting what actually you are trying to do.
Could you please share some sample data and the expected result to have a clear understanding of your question? I can do some tests for you.
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

Best Regards,
Yuna
- Anonymous
  Not applicable
  5 years ago
  Hi there
  
  Your solution was almost correct.
  
  I am trying to create a visual which shows the active vulnerabilities for each month. There should be 2 lines, one for vulnerabilities which are less than 30 days old, and one for vulnerabilities older than 30 days.
  
  I need to use the Legend field for the 'Department' column, which lists the Corporate Department affected by a given vulnerability. This would introduce a new line for each department in the column, but the idea is to use a slicer which enables the user to view just one at a time, as it might get a bit messy ( 2 lines for the age of the vulnerability * number of departments )
  if there were 2 departments then there would be 4 lines on the graph if i didnt filter out any department
  
  Hope that clears it up
Anonymous
Not applicable
5 years ago
Hi Anonymous ,

You can just merge the logic column and department column as a new column and set it as legend field.
LEDGEND = [department]&"_"&[test]
Result:
Also you can use a slicer to filter the department.

Hope that's what you were looking for.
Best Regards,
Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Anonymous
  Not applicable
  5 years ago
  Hi there,
  
  The problem with this is that I dont have a column that confirms if the occurence is N > 30 or N < 30.
  I'm trying to create one now, here is my DAX code for the new column:
  
  AGE of CRASH = if (DATEDIFF([Time of Crash ],TODAY(),DAY)>30, "N > 30 ","N < 30")
  
  How do i adjust this for M code reqs?
  
  Thanks
Anonymous
Not applicable
5 years ago
Hi Anonymous ,

I think I have replied to your question before.
If you would like to create a custom column in Power Query, you can create one as follows.
Custm=if DateTime.Date( DateTime.LocalNow())-[date] > #duration(30,0,0,0) then "earlier in 30 days" else "in 30 days"

Result:

Hope that's what you were looking for.
Best Regards,
Yuna

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