Forum Discussion
Helper II'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 ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Carol ', N'2020-11-23', N'B ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Ted ', N'2020-11-23', N'C ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Alice ', N'2020-11-23', N'D ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Bob ', N'2020-11-24', N'E ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Ted ', N'2020-11-24', N'F ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Carol ', N'2020-11-25', N'G ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Alice ', N'2020-11-25', N'H ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Bob ', N'2020-11-26', N'I ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Carol ', N'2020-11-27', N'J ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Bob ', N'2020-11-27', N'K ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Bob ', N'2020-11-28', N'L ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Carol ', N'2020-11-28', N'M ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Bob ', N'2020-11-29', N'N ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Bob ', N'2020-11-23', N'AA ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Bob ', N'2020-11-23', N'AAA ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Carol ', N'2020-11-25', N'GG ');
INSERT INTO dbo.Visits (Visitor, VisitedOn, PurposeOfVisit) VALUES (N'Carol ', N'2020-11-29', N'NN ');
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.
7 Replies
- negi007
Community Champion
ChrisNelsonPE Hi Chris, will it be possible for you to share your data in text format or share your powerbi file by hiding sensetive data
- amitchandak
Super User
ChrisNelsonPE , Try a measure like
sumX(values(Table[VisitedOn]), calculate(distinctcount(Table[Visitor])))
Hi ChrisNelsonPE ,
Could you please tell me what your expected result is?
Please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
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.
ChrisNelsonPEThanks, Anonymous . The forum software ate my original question and kept me from editing or replying to the topic for days. I started over at https://community.powerbi.com/t5/Desktop/I-m-stumped-on-analyzing-data-by-date/m-p/1529242/highlight/false#M628567 which I think has clear inputs and outputs.
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.
