Recreating an Existing Report with a new dataset

[Craig_NPDL]

Hi to the community, I'm looking for guidance on why I may be experiencing issues with a new dataset. When I first started using PBI, I created all of my visuals by importing data from Excel tables which I updated periodically. These visuals have become the primary reporting for all of my sites. I was always going to recreate the reports from the origin data so that the updates could be done automatically. Having learnt how to transform data I am at a point where I will work through transitioning the reports to be automatically updated. If I can solve the issue below, it will be the key to unlocking the whole refresh project.

In my original data (in Excel), I add data to an Excel table (BBS_Data) in which I added a column called "SimpleCount". It checked if a data field was blank, if it wasn't, its value equaled 1, this column formed the basis of the item counts that I used for my reports. I also added in a Date Table so I could use the "Month to Date" and "Year to Date" Dax Functions. The Date Table is created using the following code MyCalendar2 =CALENDAR("1/1/2020",today()), MyCalendar2 is set up as a Date Table. It has a One to Many relationship with the BBS_Data table, linked on a Date field.

I have a Line Graph visual that shows the accumulated count over the month. The measure I wrote for this visual is: 

ItemCount MTD = CALCULATE(SUM((BBS_Data[SimpleCount])),DATESMTD(MyCalendar2[Date])) +0

It produces the exact visual that I am after, accumulating over the month, the line graph going up day by day.

In my new data (imported directly from the cloud system to a table called IMS_AnalysisBBS), I added a column called "SimpleCount" to the table, the column is populated using the following function SimpleCount =IF(IMS_AnalysisBBS[Id]<>0,1,0), it is formated and matches the same column in the Excel table. I also created a Date Table called "ModelCalendar", exactly the same as above (structure and linkage).

I added data to a simple tabel to ensure that there was data being return which there is. This, however, is where I have come unstuck. When I have attempted to replicate the Month to Date function, no data is returned. The table visual shows the dates but no count is returned. The Dax funtion is below.

ItemCount MTD = CALCULATE(SUM((IMS_AnalysisBBS[SimpleCount])),DATESMTD(ModelCalendar[Date])) +0

It seems to be an issue with the Date table/link. I have tried different combinations of Dates and the Simple count but nothing is returned, the tabel comes up as below. The data is there, hopefully someone is able to tell me what my error is and how to get it out.

Regards

Craig

[Craig_NPDL]

Hi everyone

@Ashish_Mathur , I was unable to upload the requested information due to the business nature of it. @amitchandak thank you for the video links. 

I have solved this issue as I was watching the video guidance and thinking on how one set of data was working and one was not. The issue I had created was the format of the date. In the first set of data, from Excel, the time was removed from the Date field. When I extracted the data from the cloud system, all I needed to do was transform the Date field to Date only, link with the Calendar table on the Date field and the queries worked perfectly. Than you for the help and guidance.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the problem very clearly.

[Craig_NPDL]

Good morning @amitchandak, thank you for the reply. I can confirm that ModelCalendar is set up as a Date Table, linked to the main table on a date field, the measure uses the ModelCalendar Date column, the Visual also uses the ModelCalendar date field. I will review the video to see if this indicates where the problem sits. 

[amitchandak · Accepted solution]

@Craig_NPDL , Hope ModelCalendar is marked as a date table, having all the dates required. You should COlumn from ModelCalendar in Visual, slicer, and measures(seem like using in measure)

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.