Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 DateCol - Single Column - Populated using DAX: DateCol = CALENDAR("01/01/2020",TODAY()) BBS_Data - 8 Co...
  • amitchandak's avatar
    2 years ago

    Anonymous , 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-bd52912a5bd4
    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.