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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

How to capture result of a measure at certain date?

I have 3 measures(Measure1, Measure2 & Measure3) in my dataset. I have a "Sprint Begin" column in the dataset, what I wanted to do is calculate "Measure1" on every Sprint Begin date and capture that result. and calculate "Measure2" only after 7 days of Sprint Begin date, and calculate Measure3 only after 14 days of Sprint Begin date, each measure value should be captured for 14 days until the next Sprint Begin. I want to refresh each measure only every 14 days

 

I want to calculate & refresh Measure1 when Max(Sprint begin)=today() only, else show the previous value( from last refresh)..Calculate and refresh measure2 when Max(Sprint begin)+7=today() only, else show the previous value( from last refresh)...calculate and refresh measure3 when Max(Sprint begin)+13=today() only, else show the previous value( from last refresh). those measures can be shown in a card visual.

 

Team      Sprint      Sprint Begin     Sprint End  

Team1    Sprint1    1/1/2021          1/15/2021     

Team1    Sprint2    1/16/2021         1/30/2021    

Team2    Sprint1    1/1/2021           1/15/2021    

Team2    Sprint2    1/16/2021         1/30/2021    

 

I was trying to store the measures in separate new tables using summarize and refresh them on those certain days manually but my tables are big I could not even store them in sparate tables because of memory issues. Any help?

3 REPLIES 3
v-xiaotang
Community Support
Community Support

Hi @Anonymous 

The key to the problem is what to calculate, when the condition of measure1,2,3 is met. 

Knowing what conditions are met to perform calculations is not enough to solve this problem. So more details needed, and a sample file after removing sensitive information if possible.

 

Best Regards,

Community Support Team _ Tang

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

amitchandak
Super User
Super User

@Anonymous , not sure what you want to calculate here but with help from date table joined to Sprint begin date

 

you can create measures like

 

Rolling 14 days = CALCULATE(countrows(Table) ,DATESINPERIOD('Date'[Date],max(Table[Sprint begin]),14,Day))

 

Rolling 14 days after 7 = CALCULATE(countrows(Table) ,DATESINPERIOD('Date'[Date],max(Table[Sprint begin])+7,14,Day))

 

 

Rolling 14 days after 14 = CALCULATE(countrows(Table) ,DATESINPERIOD('Date'[Date],max(Table[Sprint begin])+14,14,Day))

Anonymous
Not applicable

I wanted to calculate & refresh Measure1 when Max(Sprint begin)=today() only, else show the previous value( from last refresh)..Calculate and refresh measure2 when Max(Sprint begin)+7=today() only, else show the previous value( from last refresh)...calculate and refresh measure3 when Max(Sprint begin)+13=today() only, else show the previous value( from last refresh). those measures can be shown in a card visual.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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