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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
WilliamForero
Helper I
Helper I

DAX Cambio de estado en relacion al tiempo

Hola Comunidad

 

Tengo la siguiente tabla donde quisiera contruir un estado que muestre los cambios que se presentan en el Stage_consolidado

 

ID_Operacion     |Stage_consolidado     |Fecha_corte      |PE_STAGE

111001058379   |STG 2                          |30/09/2021      |$8.199.152

111001058379   |STG 2                          |31/12/2021      |$8.199.152

111001058379   |STG 1                          |31/03/2022      |$3.249.157

111001058379   |STG 1                          |30/06/2022      |$3.249.157

111001013747   |STG 1                          |30/09/2021      |$2.147.389.688

111001013747   |STG 2                          |31/12/2021      |$447.389.688

 

Quiero ver la matriz donde se vean los cambios de estado de la siguiente manera

 

Estado                     |30/09/2021    |31/12/2021     |31/03/2022    |30/06/2022

de STG 2 a STG 1    |                       |                        | 1                   |

de STG 1 a STG 2    |                       | 1                    |                       |

 

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @WilliamForero 

Please try > measure

de STG 2 a STG 1 =
VAR CurrentStage =
    SELECTEDVALUE ( 'Table'[Fecha_corte] )
VAR CurrentDate =
    SELECTEDVALUE ( 'Table'[Stage_consolidado] )
VAR CurrentIDTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID_Operacion] ) )
VAR TableBefore =
    FILTER ( 'Table', 'Table'[Fecha_corte] < CurrentDate )
VAR PreviousRecord =
    TOPN ( 1, TableBefore, 'Table'[Fecha_corte] )
VAR PreviousStage =
    MAXX ( PreviousRecord, 'Table'[Stage_consolidado] )
RETURN
    IF ( PreviousStage = CurrentStage && CurrentStage = "STG 1", 1 )
de STG 1 a STG  2 =
VAR CurrentStage =
    SELECTEDVALUE ( 'Table'[Fecha_corte] )
VAR CurrentDate =
    SELECTEDVALUE ( 'Table'[Stage_consolidado] )
VAR CurrentIDTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID_Operacion] ) )
VAR TableBefore =
    FILTER ( 'Table', 'Table'[Fecha_corte] < CurrentDate )
VAR PreviousRecord =
    TOPN ( 1, TableBefore, 'Table'[Fecha_corte] )
VAR PreviousStage =
    MAXX ( PreviousRecord, 'Table'[Stage_consolidado] )
RETURN
    IF ( PreviousStage = CurrentStage && CurrentStage = "STG 2", 1 )

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

@WilliamForero 

Corrected formula 

de STG 2 a STG 1 =
VAR CurrentStage =
    SELECTEDVALUE ( 'Table'[Stage_consolidado] )
VAR CurrentDate =
    SELECTEDVALUE ( 'Table'[Fecha_corte] )
VAR CurrentIDTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID_Operacion] ) )
VAR TableBefore =
    FILTER ( 'Table', 'Table'[Fecha_corte] < CurrentDate )
VAR PreviousRecord =
    TOPN ( 1, TableBefore, 'Table'[Fecha_corte] )
VAR PreviousStage =
    MAXX ( PreviousRecord, 'Table'[Stage_consolidado] )
RETURN
    IF ( PreviousStage = CurrentStage && CurrentStage = "STG 1", 1 )
WilliamForero
Helper I
Helper I

Hola @tamerj1 

 

tengo este error

 

WilliamForero_0-1663875403080.png

 

@WilliamForero 

Error in line 5

Use the correct Date column

tamerj1
Super User
Super User

Hi @WilliamForero 

Please try > measure

de STG 2 a STG 1 =
VAR CurrentStage =
    SELECTEDVALUE ( 'Table'[Fecha_corte] )
VAR CurrentDate =
    SELECTEDVALUE ( 'Table'[Stage_consolidado] )
VAR CurrentIDTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID_Operacion] ) )
VAR TableBefore =
    FILTER ( 'Table', 'Table'[Fecha_corte] < CurrentDate )
VAR PreviousRecord =
    TOPN ( 1, TableBefore, 'Table'[Fecha_corte] )
VAR PreviousStage =
    MAXX ( PreviousRecord, 'Table'[Stage_consolidado] )
RETURN
    IF ( PreviousStage = CurrentStage && CurrentStage = "STG 1", 1 )
de STG 1 a STG  2 =
VAR CurrentStage =
    SELECTEDVALUE ( 'Table'[Fecha_corte] )
VAR CurrentDate =
    SELECTEDVALUE ( 'Table'[Stage_consolidado] )
VAR CurrentIDTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID_Operacion] ) )
VAR TableBefore =
    FILTER ( 'Table', 'Table'[Fecha_corte] < CurrentDate )
VAR PreviousRecord =
    TOPN ( 1, TableBefore, 'Table'[Fecha_corte] )
VAR PreviousStage =
    MAXX ( PreviousRecord, 'Table'[Stage_consolidado] )
RETURN
    IF ( PreviousStage = CurrentStage && CurrentStage = "STG 2", 1 )

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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