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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Craig_NPDL
Frequent Visitor

New to PowerBI - Help with correcting a visual

Hi, I'm after some guidance on why I cannot get a visual to show correctly.

I have 2 tables

  1. DateCol - Single Column - Populated using DAX: DateCol = CALENDAR("01/01/2020",TODAY())
  2. BBS_Data - 8 Column table - Sample view below
  3. Craig_NPDL_0-1696556018636.png

     

  4. Measure - ObsMTD = CALCULATE(SUM(BBS_Data[SimpleCount]),DATESMTD(DateCol[Date].[Date]))
  5. What I am after in the visual
    1. A line graph showing the accumulated number of items created for the month up until the current date
    2. I have a graph as below which is correct except that it shows every day of the month instead of just month to date
    3. Craig_NPDL_1-1696556348760.png

       

    4. The current filters on the visual are below, I tried to create a relative date filter but when I added one, no data was returned.
    5. Craig_NPDL_2-1696556489775.png

       

      Any help on where I have made an error would be appreciated, I hope that I have been able to add enough information to uncover the issue. If anything more is required, please let me know. Regards Craig.
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Craig_NPDL , When use time intellignece

Mark Calendar/date table as Date table

USe Calendar/date table in visual, filter/sclier and meausre

Join should 1-M single directional with fact

 

 

ObsMTD = CALCULATE(SUM(BBS_Data[SimpleCount]),DATESMTD(Calendar[Date]))

 

So not use .date.

 

Also, have explicit column in the calendar table to filter and use in visual

 

Calendar = Addcolumns(calendar(date(2012,01,01), date(2024,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "FY Year", if( Month(([DAte])) <7 , year(([DAte]))-1 ,year(([DAte])))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
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.

 

View solution in original post

2 REPLIES 2
Craig_NPDL
Frequent Visitor

@amitchandak, thank you for the guidance. I had to swap laptops and lost all of my previous data, I took your Date table solution and applied it when rebuilding and the graphs are working as expected now, greatly appreciate the help, this has unloacked answers to a number of questions I have been struggling with.

amitchandak
Super User
Super User

@Craig_NPDL , When use time intellignece

Mark Calendar/date table as Date table

USe Calendar/date table in visual, filter/sclier and meausre

Join should 1-M single directional with fact

 

 

ObsMTD = CALCULATE(SUM(BBS_Data[SimpleCount]),DATESMTD(Calendar[Date]))

 

So not use .date.

 

Also, have explicit column in the calendar table to filter and use in visual

 

Calendar = Addcolumns(calendar(date(2012,01,01), date(2024,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "FY Year", if( Month(([DAte])) <7 , year(([DAte]))-1 ,year(([DAte])))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors