Forum Discussion

pabeader's avatar
pabeader
Icon for Post Patron rankPost Patron
5 years ago
Solved

Matrix change starting row

I would like to show parts produced per hour, starting with 7 AM  today and running through 7 AM tommorow, rather than 0 AM  today and running through 23 today.  Is there a way to do this?    
Need Help
  • edhans's avatar
    edhans
    5 years ago

    If you only have 24 values - 7am, 8am, 9am, etc for all 24 periods, you could create a custom column in Power Query to do this:

    if [Column1] = "7am" then 1 else 
if [Column1] = "8am" then 2 else 
if [Column1] = "9am" then 3 else
null

    That is 3. You just need to do the other 21. There must be a else null at the end without getting too deep in if/then/else constructs.

    It is 100% case sensitive. So "7am" is not the same as "7AM"

     

    If you have minutes then this isn't workable like that. Otherwise, we'd need to see data. How to get good help fast. Help us help you.
    How to Get Your Question Answered Quickly - Give us a good and concise explanation
    How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
5 years ago

Hi pabeader , I would consider adding a 2nd date column, and then run your reports on that column. For example:

if DateTime.Time([Date]) < #time(7,0,0) then
Date.AddDays(DateTime.Date([Date]), -1)
else DateTime.Date([Date])

So any times before 7am get a day subtracted. Any times on or after 7am stay in the current day. You could even then remove the original date and just keep the "New Date" field, or however you want to report it.

 

pabeader's avatar
pabeader
Icon for Post Patron rankPost Patron
5 years ago

I think this breaks Direct Query...