Forum Discussion

Sankzpower's avatar
Sankzpower
Helper I
6 years ago
Solved

Missing dates visibility in a table/Visual

Hi Guys   A very strange one for me. I have tried different solutions such as counting the days between from and to but none of seem it work. Below is my dataset. Ultimately, I would like Power bi ...
Need Help
Show and Tell
  • lc_finance's avatar
    lc_finance
    6 years ago

    Hi Sankzpower ,

     

     

    you can download my proposed solution from here.

     

    I did the following:

    1) create a new calendar table by ID.

    Date by ID = GENERATEALL(CALENDARAUTO(), VALUES('Days by ID'[ID]))

    2) Add to the calendar table a new column that checks if the date is included in the main table

    Included = 
var currentID = [ID]
var currentDate = [Date]
RETURN
COUNTX(FILTER('Days by ID','Days by ID'[ID]=currentID && 'Days by ID'[DateFrom]<=currentDate && 'Days by ID'[DateTo]>=currentDate),[Value] )

    3) Add one measure to count the missing days

    Count of missing days = COUNT([Date])-COUNTX('Date by ID',[Included])

    4) Add one measure that returns 'missing' if there are missing days

    Check if missing = IF([Count of missing days]<>0, "Missing")

     

    Below is a screenshot:

     

    I hope this helps you. Do not hesitate if you have further questions

     

    LC

    Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

lc_finance's avatar
lc_finance
Solution Sage
6 years ago

Hi Sankzpower ,

 

 

you can download the updated solution from here.

 

I added 2 columns in Days by ID:

- a column to calculate the number of days between DateFrom and DateTo

Days = DATEDIFF([DateFrom],[DateTo],DAY)+1

- a column to calculate the Value per day, which is the value divided by the number of days

Days = DATEDIFF([DateFrom],[DateTo],DAY)+1

 

And I added one column in Date by ID, which uses the [Value per day] calculated previously

Value per day = 
var currentID = [ID]
var currentDate = [Date]
RETURN

SUMX(FILTER('Days by ID','Days by ID'[ID]=currentID && 'Days by ID'[DateFrom]<=currentDate && 'Days by ID'[DateTo]>=currentDate),
 [Value per day]
)

 

 Let me know if this is what you are looking for!

 

Regards

 

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

Sankzpower's avatar
Sankzpower
Helper I
6 years ago

lc_finance Thanks a lot again. Its just simple thing but excuting is a big challenge as in the sense that I could do it excel but delivering via power bi is a big task. You absolutely nailed it. 

  • lc_finance's avatar
    lc_finance
    Solution Sage
    6 years ago

    Thank you Sankzpower  for the kind words!

     

    Your comment made my day

     

    LC

  • Sankzpower's avatar
    Sankzpower
    Helper I
    6 years ago

    Hi lc_finance  

     

    Sorry to bug again. I stuck with this and tried nearly some time but cant get it work. Again, its easier to put into excel and pivot the result what I need but would love to get this working in power bi. 

     

    I am looking for a measure to show total number of column count by id. I have exported the file output from power bi to excel and simply pivoted here Sample file . Just need the pivot table visual in power bi but again stuck.

     

    Any help would be appreciated. Thank you