Limit data flowing in until a certain date

Question

This is quite complex so I will try to explain it as simple as possible.

The chart below shows weekly utilization.

I have set my date locally on Tuesday 26 June 2018 deliberately because what I want is to be able to bring in data for the current week only from Wednesday and onwards and not from Sunday when the week starts.

In other words, having set my date to 26th of June (Tuesday) I would like to limit the data reflected on the green bar for the week of 30 Jun in the chart. This line should only be appeared the next day, which would be Wednesday, but until then I wouldn't like BU% (which is the calculated measure that is reflected) to be appeared.

The measure which generates this green column is the following:

Billable Util_CDL_WEEK = CALCULATE(SUM('cdl weekly'[Hours]),'TIME CATEGORY'[Time Category All]="Billable")/CALCULATE(SUM('cdl weekly'[Hours]),'TIME CATEGORY'[Time Category All]="Available"))

I tried the following

Billable Util_CDL_WEEK = IF(WEEKDAY(TODAY(),1)<4,"-",CALCULATE(SUM('cdl weekly'[Hours]),'TIME CATEGORY'[Time Category All]="Billable")/CALCULATE(SUM('cdl weekly'[Hours]),'TIME CATEGORY'[Time Category All]="Available"))

but it disappears all previous weeks results which is obviously wrong.

Is there any way to manipulate this ?

greg_deckler · Answer

Have any sample/example data?&nbsp;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

v-juanli-msft · Answer

Hi&nbsp;harrinho
Try formula below&nbsp;
Billable&nbsp;Util_CDL_WEEK&nbsp;2=IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WEEKNUM&nbsp;(&nbsp;[date]&nbsp;)&nbsp;=&nbsp;WEEKNUM&nbsp;(&nbsp;DATE&nbsp;(&nbsp;2018,&nbsp;6,&nbsp;30&nbsp;)&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WEEKDAY&nbsp;(&nbsp;[date]&nbsp;)&nbsp;&lt;&nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;0,&nbsp;&nbsp;&nbsp;&nbsp;[Billable Util_CDL_WEEK])

&nbsp;
Best Regards
Maggie

harrinho · Answer

Thanks for the reply&nbsp;v-juanli-msft. I don't have the date fields you suggest here. How could that be addressed please ?&nbsp;&nbsp;

v-juanli-msft · Answer

Hi&nbsp;harrinho
Which column do you add to the Axis of the bar chart?
The date column in my formula should be in date type and be related with the columns added to your&nbsp;Axis.
&nbsp;
Best Regards
Maggie

harrinho · Answer

v-juanli-msft&nbsp;The column added in the chart (x axis) is a text field which shows the week ending. I do have a date type column of the week ending as well but it is not in the table that I am creating this calculation and it doesn't show it up when I type it.&nbsp;&nbsp;When I type for example:&nbsp;&nbsp;Billable Util_CDL_WEEK 2 = 
IF (
    AND (
        WEEKNUM ([Week Ending] ) = WEEKNUM ( DATE ( 2018, 6, 30 ) ),
        WEEKDAY ( [Week Ending] ) &lt; 4
    ),
    0,
    [Billable Util_CDL_WEEK]
)It doesn't recognize the "Week Ending"&nbsp;

Forum Discussion

Limit data flowing in until a certain date

7 Replies

Related content

New limits in Power BI Data Flows

Copilot, limitations?

Translytical Flows

Data limit Issues

Get DP-700 for FREE — Limited Offer