Forum Discussion

powerbifuddaa's avatar
powerbifuddaa
Helper II
3 years ago

earliest date when using a visual

Hi there,

Hope someone can help me.

I made a table that shows movements of id's from segment a to b (see formula below). The startdate should be made "earliest".

Works perfectly fine (270 rows).

However, now I would like to make a visual of this with the "earliest" startdate on the x-axis. When I do this, "earliest" is ignored and all the startdates are shown (1156 rows) like this:

 

 

 I would like to show this:

and this:

This is the formula I use to identify the id's segment movements:

IF(OR(RELATED('Employment type'[EmploymentType_BK])="t",RELATED('Employment type'[EmploymentType_BK])="v"),
IF("E02" in SELECTCOLUMNS(FILTER('Contract History', 'Contract History'[Person_BK]=EARLIER('Contract History'[Person_BK])),"1",RELATED('Employment type'[EmploymentType_BK])),1,0),0)

 

Hope someone can help me with this. Thanks!

(I can only use DAX)

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    powerbifuddaa , Create a date table and have Month and year in that table. Create a bar visual with concatenate label off

     

    Date= 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")
    )
    )

     

     

    Join with date of your table

    • powerbifuddaa's avatar
      powerbifuddaa
      Helper II
      3 years ago

      Thanks amitchandak 

      My problem is that I have to work in my vendor's analysis services surroundings so I only have DAX (no power query, no create table options).

      So, my challenge is particularly that with the "earliest" date, the "earliest" mutation is picked (270) and not all mutations what happens if I "don't summarize" (1156).