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
Till__
Helper I
Helper I

Categorization of values at specific dates

Dear all, 

 

I have the following table with name "Härtegrad" with two measures which have a specifc value at a time and the value only changes if the category changes for a measures. My goal is to achieve a graph over time, where I can see at each date how much Savings are in a specifc category.  

Till___2-1677434476656.png

 

Above you can see that at the 10. September 2022 6 are in category IL 1 and on the 10. Oktober 2022 56 of total savings are in category 1. The difficulty for me occurs on the 11. Oktober when I have total savings of 57 but 7 are in Category IL 2 and 50 still remain in category IL1. At the last date the 24. Dezember 2024 a total value of 70 is again then in the last category IL5. 

Till___0-1677436958732.png

 



DateCategory =
GENERATE(
    DISTINCT('Härtegrad'[Date of Change]),
    DISTINCT('Härtegrad'[Category])
)

I was trying to work with a new table "DateCategory" which makes a distinction of every date and assings each category to it. 
However, the problem for me is now that I am not able to caluclate the correct value in each category at each date of change. 

 

Using the example from above I want to achieve that at the 10. September 2022 all entries in the values columns are zero except in Category IL1 a value of 6 is existing. At the 10. Oktober 2022  all entries in the values columns are zero except in Category IL1 with a value of 56. At the 11. Oktober 2022 only three category matches are zero but in category IL1 a value of 50 and in category IL2 a value of 7 (which was 6 before - that is the saving in a category can change) is observed. This goes then until the end when at the 24. Dezember 2024  all corresponding entries in the values columns are zero except the one matching he Category IL 5 with a value of 70. 

I was trying to code the value column with this code: 

Value =
CALCULATE(
    SUM('Härtegrad'[Savings]),
    FILTER(
        ALL('Härtegrad'),
        'Härtegrad'[Date of Change] <= MAX('DateCategory'[Date of Change]) &&
        'Härtegrad'[Category] = MAX('DateCategory'[Category])
    )
)


However this assigns to each date and category a value of 56. I am extremely frustrated and do not know any real or other solution that would work. 

Thank you all in advance!!

Best Till 



 

2 REPLIES 2
amitchandak
Super User
Super User

@Till__ , I think need to have an independent date table  and a category table. Date you can keep independent without join and category you can join and use in slcie r

 

Caregory = DISTINCT('Härtegrad'[Category])

 

Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

 

 

Value =
CALCULATE(
SUM('Härtegrad'[Savings]),
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
)
)

If I use your value calculation this does not work for me. I do have an independent Calendar table but the matching with the Category and Date of Change still does not work... 

Till___0-1677484760935.png

 



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

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.