Forum Discussion

RES's avatar
RES
Regular Visitor
1 year ago
Solved

monthly percent

I have data that looks like this. I would like a column table that shows the percent of each month including the month between. PeakPct TierStartDate TierEndDate 100% Sep 21 Sep 22 100%...
  • lbendlin's avatar
    lbendlin
    1 year ago

    To report on things that are not there you need to use disconnected tables and/or crossjoins.  That includes a calendar table that needs to cover the entire period.

     

    First, bring your data into a usable form

     

     

    Then add the calendar, for example via

     

    Dates = ADDCOLUMNS(CALENDAR("2021-09-01","2025-12-31"),"Year",FORMAT([Date],"yyyy"),"Month",FORMAT([Date],"mmm"),"MNo",FORMAT([Date],"mm"))

     

    And then add a measure that checks if the date on the calendar overlaps with the date range in the percentage table.

     

     

     

     

     

     

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

To report on things that are not there you need to use disconnected tables and/or crossjoins.  That includes a calendar table that needs to cover the entire period.

 

First, bring your data into a usable form

 

 

Then add the calendar, for example via

 

Dates = ADDCOLUMNS(CALENDAR("2021-09-01","2025-12-31"),"Year",FORMAT([Date],"yyyy"),"Month",FORMAT([Date],"mmm"),"MNo",FORMAT([Date],"mm"))

 

And then add a measure that checks if the date on the calendar overlaps with the date range in the percentage table.

 

 

 

 

 

 

 

  • RES's avatar
    RES
    Regular Visitor
    1 year ago

    This is beautiful! Thank you!