Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lefinalzugzwang
Frequent Visitor

calculating the number of periods in which a budget was exceeded

Hi,

I am aiming to create a measure to calculate the number of periods in which a budget was exceeded.

In my specific case, what I am trying to do is count the number of Dates[Iso.Week] in which Sales[Sale.Value] for Dates[Iso.Year]=2019 exceeded Sales.[Sale.Value] for Dates[Iso.Year]=2018.

Any help would be greatly appreciated, I think I am really struggling to wrap my head around filter contexts ...

Best wishes,
Laurence
_____

p.s.
Dates[Iso.Year] is the Year as defined by week numbers
Dates[Iso.Week] is the week number of a given date
Sales[Sale.Value] is the raw sales value for transactions (which are all dated and linked to the above two fields).

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

@lefinalzugzwang,

 




I am aiming to create a measure to calculate the number of periods in which a budget was exceeded.

In my specific case, what I am trying to do is count the number of Dates[Iso.Week] in which Sales[Sale.Value] for Dates[Iso.Year]=2019 exceeded Sales.[Sale.Value] for Dates[Iso.Year]=2018.

Any help would be greatly appreciated, I think I am really struggling to wrap my head around filter contexts ...

Best wishes,
Laurence
_____

p.s.
Dates[Iso.Year] is the Year as defined by week numbers
Dates[Iso.Week] is the week number of a given date
Sales[Sale.Value] is the raw sales value for transactions (which are all dated and linked to the above two fields).


Could you share some sample data and clarify more details about your current measures and expected result?

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Jimmy, sure ...

For example

Dates[Iso.Week]=1, Sales[Sale.Value] for Dates[Iso.Year] 2018 = £0.21M, Sales[Sale.Value] for Dates[Iso.Year] 2019 = blank       -False

Dates[Iso.Week]=2, Sales[Sale.Value] for Dates[Iso.Year] 2018 = £0.54M, Sales[Sale.Value] for Dates[Iso.Year] 2019 = £0.73M    -True

Dates[Iso.Week]=3, Sales[Sale.Value] for Dates[Iso.Year] 2018 = £0.26M, Sales[Sale.Value] for Dates[Iso.Year] 2019 = £0.44M    -True

Dates[Iso.Week]=4, Sales[Sale.Value] for Dates[Iso.Year] 2018 = £0.70M, Sales[Sale.Value] for Dates[Iso.Year] 2019 = £0.82M    -True

Dates[Iso.Week]=5, Sales[Sale.Value] for Dates[Iso.Year] 2018 = £0.55M, Sales[Sale.Value] for Dates[Iso.Year] 2019 = £0.54M    -False

Measure = number of weeks so far this year where 2019 exceeded 2018 = 3

 

Hope this makes things clearer.

Best wishes,
Laurence

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.