Forum Discussion

test_123_e213's avatar
test_123_e213
Frequent Visitor
7 years ago
Solved

Grouping YTD by weeks not having intended effect

I want to group my data by week, but then the data stops accumulating as shown above. Is there any way to group my YTD data by week? Month would also be great (Sample data is used in the ta...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    OK, this may not be what you want but maybe it gets you closer. I took your first table and I created this column:

     

    WeekNum = WEEKNUM([YTDcol2])

    Then I created these two measures:

     

    AccumShift_Dur_Hrs running total in WeekNum = 
CALCULATE(
	SUM('Table12'[AccumShift_Dur_Hrs]),
	FILTER(
		ALLSELECTED('Table12'[WeekNum]),
		ISONORAFTER('Table12'[WeekNum], MAX('Table12'[WeekNum]), DESC)
	)
)

YTDdelayHrs running total in WeekNum = 
CALCULATE(
	SUM('Table12'[YTDdelayHrs]),
	FILTER(
		ALLSELECTED('Table12'[WeekNum]),
		ISONORAFTER('Table12'[WeekNum], MAX('Table12'[WeekNum]), DESC)
	)
)

    I could then put my WeekNum column and these two measures in a table visualization and get a running total for each. Is that what you are going for?

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago

Can you post your sample source data so that it can be copied and pasted? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

test_123_e213's avatar
test_123_e213
Frequent Visitor
7 years ago
YTDcol2AccumShift_Dur_HrsYTDdelayHrs
1/1/16 12:00 AM241.35
1/2/16 12:00 AM486.22
1/3/16 12:00 AM727.66
1/4/16 12:00 AM9611.21
1/5/16 12:00 AM12014.32
1/6/16 12:00 AM14438.21
1/7/16 12:00 AM16838.93
1/8/16 12:00 AM19841.25
1/9/16 12:00 AM21642.12
1/10/16 12:00 AM24042.51
1/11/16 12:00 AM26443.66
1/12/16 12:00 AM28844.21
1/13/16 12:00 AM31245.76
1/14/16 12:00 AM33651.56

 

WeekYearYTDmeasure2YTDdelayhrsUnplanned
2018-1969.52
2018-216822.13
2018-316818.71
2018-416821.23
2018-516844.25
2018-6168

16.42

 

WeekYearYTDmeasure2YTDdelayhrsUnplanned
2018-1969.52
2018-226431.65
2018-343250.36
2018-460071.59
2018-5768115.84
2018-6936132.26

 

Note that the data is just for an example to understand the problem. 

 

Thanks Greg. ~Lucas

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    OK, this may not be what you want but maybe it gets you closer. I took your first table and I created this column:

     

    WeekNum = WEEKNUM([YTDcol2])

    Then I created these two measures:

     

    AccumShift_Dur_Hrs running total in WeekNum = 
CALCULATE(
	SUM('Table12'[AccumShift_Dur_Hrs]),
	FILTER(
		ALLSELECTED('Table12'[WeekNum]),
		ISONORAFTER('Table12'[WeekNum], MAX('Table12'[WeekNum]), DESC)
	)
)

YTDdelayHrs running total in WeekNum = 
CALCULATE(
	SUM('Table12'[YTDdelayHrs]),
	FILTER(
		ALLSELECTED('Table12'[WeekNum]),
		ISONORAFTER('Table12'[WeekNum], MAX('Table12'[WeekNum]), DESC)
	)
)

    I could then put my WeekNum column and these two measures in a table visualization and get a running total for each. Is that what you are going for?

    • test_123_e213's avatar
      test_123_e213
      Frequent Visitor
      7 years ago

      Wow this works great!

       

      Thanks!

       

      Would you know if there's a way to have this work with my WeekYear label?

       

      It's nice to display 2018-1, 2018-2 on the graph.

       

      The goal, but with accumulating dataWhat I currently have

      I've just been trying and can't get it to work.

       

      Thanks for your help. No worries if I can't get this second part to work. 

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Well, if you WeekYear is in the same table as the original data then you should just use that in your table visualization rather than the WeekNum. Should work identically as the measure should have the correct context to calculate correctly. Or is your WeekYear in a different table? I am thinking it is a table like:

         

        Date,WeekNum,WeekYear,Column1,Column2,Column3