cancel
Showing results for
Did you mean:
Billy0503 Frequent Visitor

Urgent help needed!

Hello everyone,
I´m dealing about some weeks with a difficult task and its driving me crazy The case is that I have 12 Months for a year with amount in \$ and each month I has different versions of data. So that means for January I have the Version 01-2018, but it has data for the whole year. In February I have the version 02-2018 with new and other data in there, and so on. So for my report I need to make a comparison where I have to compare two versions (see at the picture below). In the month which we actually are it´s the current cycle (CC in the column), and the previous month is called last cycle (LC in the column). The problem is that the last cycle always has to be without the month which the currenc cycle is (see the formula 2). Till here it worked fine for me, but now I am searching for an idea or a solution, which has a measure with a automatically formula.  My idea would be, to have to measures.
1. Current cycle CC: which takes always the latest version. Something like date max or max value ( i dont know)
2. Last cycle LC: takes automatically the version before the current cycle AND without the month, in which the latest version is.

Example:
Current cycle is from the version 02-2018 which has February as a month with data. Last cycle would be 01-2018, without the February from the version 02-2018.

I hope I explained it as much as good enough for you to understand my problem And I hope really that someone could help with this because it is very important to me. If you need some aditional information, please let me know.

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor

Re: Urgent help needed!

Hi @Billy0503,

If I understand you correctly, you should be able to follow steps below to get the expected result. 1. Use the formula below to add a new calculate column in FORECAST_INFORMATION table.

FC_DATE_VALUE =
VALUE ( RIGHT ( FORECAST_INFORMATION[FC_DATE], 4 ) ) * 12
+ VALUE ( LEFT ( FORECAST_INFORMATION[FC_DATE], 2 ) )

2. Use the formula below to create a new measure.

Current Cycle Date =
CALCULATE (
MAX ( FORECAST_INFORMATION[FC_DATE] ),
ALL ( FORECAST_INFORMATION )
)

3. Then change your exiting measure like below.

W.CC =
SUMX (
FILTER (
FC_SALES_DATA,
RELATED ( FORECAST_INFORMATION[FC_DATE_VALUE] ) = [Current Cycle Date]
),
FC_SALES_DATA[W. TOTAL TRUE CY]
)
W.LC =
SUMX (
FILTER (
FC_SALES_DATA,
RELATED ( FORECAST_INFORMATION[FC_DATE_VALUE] )
= [Current Cycle Date] - 1
),
FC_SALES_DATA[W. TOTAL TRUE CY]
)
- FC_SALES_DATA[WEITHT_NOV_TURE_CY]

Regards

v-ljerr-msft Super Contributor

Re: Urgent help needed!

Hi @Billy0503,

If I understand you correctly, you should be able to follow steps below to get the expected result. 1. Use the formula below to add a new calculate column in FORECAST_INFORMATION table.

FC_DATE_VALUE =
VALUE ( RIGHT ( FORECAST_INFORMATION[FC_DATE], 4 ) ) * 12
+ VALUE ( LEFT ( FORECAST_INFORMATION[FC_DATE], 2 ) )

2. Use the formula below to create a new measure.

Current Cycle Date =
CALCULATE (
MAX ( FORECAST_INFORMATION[FC_DATE] ),
ALL ( FORECAST_INFORMATION )
)

3. Then change your exiting measure like below.

W.CC =
SUMX (
FILTER (
FC_SALES_DATA,
RELATED ( FORECAST_INFORMATION[FC_DATE_VALUE] ) = [Current Cycle Date]
),
FC_SALES_DATA[W. TOTAL TRUE CY]
)
W.LC =
SUMX (
FILTER (
FC_SALES_DATA,
RELATED ( FORECAST_INFORMATION[FC_DATE_VALUE] )
= [Current Cycle Date] - 1
),
FC_SALES_DATA[W. TOTAL TRUE CY]
)
- FC_SALES_DATA[WEITHT_NOV_TURE_CY]

Regards

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 334 members 3,691 guests
Recent signins:
• pedromurgel • viacka • nandukrishnavs • webportal • landries1988 • MarkCNB • DM_BI • timber • Jerremans • Panigrahi • BrandynL914 • robrechts • lbolka01 • APASTORM 