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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ribaldo
Frequent Visitor

Calculate quarter as difference between YTD months

Hi all.

I've data stored in my database as YTD values, and YTD data are not a sum of months.

I'm looking for a way to calculate quaters not as an aggregation of months but as a difference between YTD values of the last month of the quarter.

Fo example:

     Expeted Result
YearQuarterMonthSales YTD AmtSales Amt 
2021 January1010 
  February2313 
  March3412 
 Q1  3534
  April4711 
  May6014 
  June7313 
 Q2  3839

 

Any ideas?

 

2 REPLIES 2
ribaldo
Frequent Visitor

@amitchandak , I've some trouble implementing your solution. I'm using tabular with compatibility level 1400, and don't have isinscope function. I tried hasonevalue, but without success.

amitchandak
Super User
Super User

@ribaldo , You need a measure like this

 

if(isinscope(Date[Qtr]) && Not(isinscope(Date[Month])) , [sales YTD] - calculate([sales] -datesmtd( Date[Date])), blank())

 

Assuming you are using date table and time intelligence

 


YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors