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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
simaotc
Frequent Visitor

Stange behaviour of calculation groups

Hello dear comunity,

 

I have a financial dashboard with two main tables.

 

A date table and a table with all accounting movements, including movements between companies of the group.

 

First, I createt a calculation group for dynamic periods, like QTD, YTD, Last 6 Months, ...

This is the base code:

VAR _datetable = 'Date'
VAR _today = TODAY()
VAR _month = MONTH(TODAY())
VAR _quarter = QUARTER(TODAY())
VAR _year = YEAR(TODAY())
VAR _thisquarterstart = DATE(_year, SWITCH(TRUE(), _quarter = 1,1, _quarter=2,4, _quarter=3,7,10) ,1)
VAR _previousquarterstart = EDATE(_thisquarterstart, -3) // New variable for the start of the previous quarter
VAR _thisyearstart = DATE(_year,1,1)


VAR _QTD = FILTER('Date','Date'[Date]<=_today && 'Date'[Date] >= _thisquarterstart)

VAR _YTD = FILTER('Date','Date'[Date]<=_today && 'Date'[Date] >= _thisyearstart)

VAR _PreviousQuarter = FILTER('Date','Date'[Date] < _thisquarterstart && 'Date'[Date] >= _previousquarterstart)

VAR _Last6Months = FILTER('Date','Date'[Date]>_today-181)

VAR _Last12Months = FILTER('Date','Date'[Date]>_today-366)



RETURN
CALCULATE ( SELECTEDMEASURE(), _Last6Months )


It filters in both table but in the table of the previous year stops doing the YTD:

simaotc_0-1702860601714.png

 

The second question is about the aggregated transactions between companies, for that I created another calculation group, where the user can select the company A, company B and movements between Company A - Company B (all movements of company A + all movements of company B - movements between the two companies):


VAR _A = FILTER('Movements','Movements'[Company] = "A")

VAR _B = FILTER('Movements','Movements'[Company] = "B")

VAR _A_B = 
FILTER(
    'Movements',
    ('Movements'[Company] = "A" || 'Movements'[Company] = "B") &&
    ('Movements'[InterCo] <> "A-B")
)
RETURN
CALCULATE ( SELECTEDMEASURE(), _A )



But This doesn't work - both tables I got monthly values and all the values in second table are equal to the first table:

simaotc_1-1702861223154.png

 

I know it's confusing but any help would be incredibly appreciated

 

Thank you,

Simao

 

 

1 REPLY 1
amitchandak
Super User
Super User

@simaotc , The calculation group do not seem fine to me refer how the time based and other meausre based calculation groups are created and used

 

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

 

Model explorer public preview with calculation group authoring| Measure Slicer: https://youtu.be/VfxfJJ0RzvU

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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