Forum Discussion

ChrisNelsonPE's avatar
ChrisNelsonPE
Icon for Helper I rankHelper I
5 years ago
Solved

I'm stumped on analyzing data by date

CREATE TABLE Visits ( Visitor NCHAR(10), VisitedOn DATE, PurposeOfVisit NCHAR(10) ) GO INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Bob ', N'2020-11-23', N'A '); INSE...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

     Hi ChrisNelsonPE ,

     

    For a bar chart, you try the measures.

    Measure = CALCULATE(DISTINCTCOUNT('Table'[Visitor]),FILTER('Table',[VisitedOn]=MAX('Table'[VisitedOn])))

     

    Just use FILTER function to filter the dates which you want. MAX corresponds to the latest date, and MIN corresponds to the earliest date.

     

    New sample pbix file is here.

     

     

    Best Regards,

    Stephen Tao

     

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi ChrisNelsonPE ,

 

For the first expected result, put the VisitedOn column and the Visitor column to a table. Then select Count(Distinct) in the Visitor column as follows.

 

For the second expected result, put the VisitedOn column and the Visitor column to a table. Then select Lateset in the VisitedOn column as follows.

 

You can check more details from here.

 

 

Best Regards,

Stephen Tao

 

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

ChrisNelsonPE's avatar
ChrisNelsonPE
Icon for Helper I rankHelper I
5 years ago

Anonymous, thanks, that does produce the correct results.  But I need charts, not tables.  I tried to create a bar chart with the same process but some of the options aren't there.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     Hi ChrisNelsonPE ,

     

    For a bar chart, you try the measures.

    Measure = CALCULATE(DISTINCTCOUNT('Table'[Visitor]),FILTER('Table',[VisitedOn]=MAX('Table'[VisitedOn])))

     

    Just use FILTER function to filter the dates which you want. MAX corresponds to the latest date, and MIN corresponds to the earliest date.

     

    New sample pbix file is here.

     

     

    Best Regards,

    Stephen Tao

     

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