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
Adrianna
Regular Visitor

Incomplete data for YoY%

Hey! 

 

I am trying to visualize yearly change of the value. I encountered one problem, everything works fine until I get to the 2018. I've got missing data from April 2018 and it keeps on lowering my yearly change because the data is summarising whole year. Please have a look at the code first:

 

 

code2.PNG

Code1.PNG

 

hereresult1.PNG you can find how my calculations are done and the matrix shows the results for each month, unfortunatelly the card summarises it all and prints the summary for all year, regardless from its availability. I would also like ot make it flexible, as soon as i manage to fetch new data, I would like it to adjust.

 

I tried various methods like trying to filter max(month) od max(date) although neither of the ways worked, hope you can suggest me something.

 

Thanks 🙂

4 REPLIES 4
gooranga1
Power Participant
Power Participant

Hi @Adrianna

 

Do you have a date dimension? If you do then create a column in the date dimension like

 

FutureDay = if([Date]>date(year(now()),month(now()),day(now())) ,1,0)

Then Set a Page/Visual/Report filter to set this parameter to filter only 0. 

 

Not sure if it will work if you add this to your KNF table, I don't see why not.

The problem is that such dates do not figue in my dataset - they are created as a calculation while I refer to the dates from the previous year 😞

dani0010
Frequent Visitor

I encounter similar situations frequently, and my pattern is to always create a measure which I call Pivot Date. Pivot Date contains the latest date of historical data. Use whatever logic you need to arrive at this date. Then you can use a statement like  min(pivot date, max(date dimension)) to filter your dates to the proper cutoff. This will work properly for prior and current years.

 

Pivot Date is very handy for doing prior period to-date calculations or if you're doing forecasting and need future dates in your calendar but also need to keep track of the cutoff of historical actuals.

Thank you @dani0010 🙂

 

I've tried: Pivot_Date = max('KNF'[Date])

and applied it here: 

 

code3.PNG

 

Although it doesn't seem to work. Would you mind sharing a way to implement it? So that I will get the main idea of using it? 

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.